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