Saturday, August 2, 2014

Sql Server -Group Concat using STUFF & For XML



Let’s first create a table to demonstrate the concept as follows    

create table #Temp
(
  id int,
  fname varchar(50),
  lname varchar(50)
)

Now I will add data into it in specific format as follows
insert into #Temp(id,fname,lname)
select '70','sangram',null
union
select '70',null,null
union
select '70',null,'desai'


insert into #Temp(id,fname,lname)
select '60','sachin',null
union
select '60',null,null
union
select '60',null,'desai'














In all now our #Temp table is having 6 records in it.


We aim to convert this data into two rows by doing group concatenation over string in fname & lname columns respectively 

    Here is my take over it.

    select
        distinct id ,
      STUFF(
           (
            Select ','+fname
                from #Temp T1
                where T1.id=T2.id


              FOR XML PATH('')
          ),1,1,''),
          STUFF((Select ','+lname
                from #Temp T1
                where T1.id=T2.id
            FOR XML PATH('')),1,1,'')
    from
       #Temp T2


This will collect value from first row of fname & append it with value from second row fname over rowset under consideration,value concatenation is done with comma as separator that can be visible if multiple non-null values occur in fname column in given row-set

Output:
 
 
     Thanks & Happy Coding !