SQL Server - how to add more than one columns in to single vice versa?

Asked By Nani on 05-Jan-11 02:46 AM

Hi Developers,

i have following Requirment in Sql server 2005,

IDs
10,20,30,40

OUT PUT should be :

IDs
10
20
30
40

to acheive above how can i write Querry?
Please Help me?

Best Regards,
Nani

Web Star replied to Nani on 05-Jan-11 02:59 AM

your query is little bit confusion your are write
OUT PUT should be :

IDs
10
20
30
40

Where as most of the table contain id like that so you just
Select id from table name you will get that type of output

if as you show
IDs
10,20,30,40

in you table all id are stored in one column like above than you need to populate it like your desired result for that first get the vlaue of column and than split that string on comma and put it in arrary after thant you will do what you want

Split Function

Lalitha Kumaran replied to Nani on 05-Jan-11 03:10 AM

Create split function and pass the Input value(10,20,30,40) and Delimeter(,) to get the values in each row.

select * from Split('10,20,30,40',',')

items
------
10
20
30
40

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
    declare @idx int
    declare @slice varchar(8000)

    select @idx = 1
        if len(@String)<1 or @String is null  return

    while @idx!= 0
    begin
        set @idx = charindex(@Delimiter,@String)
        if @idx!=0
            set @slice = left(@String,@idx - 1)
        else
            set @slice = @String

        if(len(@slice)>0)
            insert into @temptable(Items) values(@slice)

        set @String = right(@String,len(@String) - @idx)
        if len(@String) = 0 break
    end
return
end

--------------------------------------------------------------------------------------------------

Once you create this function , execute this select query to get the output as you expected

select * from split(ids,',')

pass the table column as input for split function

Lalitha Kumaran replied to Lalitha Kumaran on 05-Jan-11 03:30 AM

Table : d1

id
--
10,20,30,40

Use the Split function to get the results .

select A.items
from
(select * from d1 d cross apply Split(d.id,',')) A

Items
------
10
20
30
40

Nani replied to Web Star on 05-Jan-11 03:52 AM

hi i want in both ways.

thank u

Get rows into comma sepated columns

Lalitha Kumaran replied to Nani on 05-Jan-11 05:13 AM

Table d2:

items
------
10
20
30
40

SELECT STUFF(( SELECT ', ' + [items] FROM (SELECT [items] FROM d2) AS T FOR XML PATH('') ) ,1,1,'') AS [items]

items
------
10, 20, 30, 40

Bildoss replied to Nani on 05-Jan-11 09:51 AM

Hi Nani,

Please find the below script

create function [dbo].[fn_get_skill_id] (@fi_skill_id as nvarchar(max))

returns @temp_skill_id table (id nvarchar(100))

as

begin

/*******************************************************************

Success Execution

------------------------

select * from fn_get_skill_id ('3453,31231')

select * from fn_get_skill_id ('3453')

********************************************************************/

declare @l_incr int ,

@l_str nvarchar(100)

set @l_incr=1

set @l_str=''

while (@l_incr<= len(@fi_skill_id))

begin

if substring(@fi_skill_id,@l_incr,1)<>','

set @l_str=@l_str + substring(@fi_skill_id,@l_incr,1)

else

begin

insert into @temp_skill_id values(@l_str)

set @l_str=''

end

set @l_incr=@l_incr+1

end

insert into @temp_skill_id values(@l_str)

return

end