How to transform a single concatenated string into individual rows and vice versa

Check list of all posts

There are many different ways to get it done in SQL server. The listed approach below is very powerful and convenient

Transforming a single concatenated string into individual rows

go

create table #Test( ID int,  Data varchar(max))

insert #Test select 1, 'A,B,C'

insert #Test select 2, 'D,E'

select * from #Test

select T.ID,  

    X.T.value('.', 'VARCHAR(100)') AS Data  

from  

(

    select ID,  

        CAST ('<M>' + REPLACE(Data, ',', '</M><M>') + '</M>' AS XML) AS Data  

    from  #Test

) AS T CROSS APPLY Data.nodes ('/M') AS X(T);

drop table #Test

Transforming row values in a table to a single concatenated string

go

create table #Test( ID int,  Data varchar(10))

insert #Test select 1, 'A'

insert #Test select 1, 'B'

insert #Test select 1, 'C'

insert #Test select 2, 'D'

insert #Test select 2, 'E'

select * from #Test

select distinct

ID,

STUFF((Select ','+ Data

from #Test T1

where T1.ID=T2.ID

FOR XML PATH(''))

,1,1,''

)

from #Test T2

drop table #Test