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 !