Sharvil Popli Sharvil Popli - 16 days ago 8
SQL Question

Split string in SQL for mixed and particular comma separated values?

How can I split string in SQL Server 2012 for such a table:

| ID | Data |
+-----+----------+
| 1 | abc,def |
| 2 | def,abc |
| 3 | def,ghi |


into this:

| ID | Data1 | Data2| Data3 |
+-----+-------+------+-------+
| 1 | abc | def | null |
| 2 | abc | def | null |
| 3 | null | def | ghi |


My aim is to get all abc's | def's | ghi's into columns of their own.

Answer

Used a #Temp table to store the parsed results. Perhaps you can migrate into a cte

Declare @YourTable table (ID int,Data varchar(max))
Insert Into @YourTable values
(1,'abc,def'),
(2,'def,abc'),
(3,'def,ghi')

Select A.ID
      ,B.*
      ,ColName = 'Data'+cast(DENSE_RANK() over (Order By RetVal) as varchar(25))
Into #Temp
From @YourTable A
Cross Apply (
              Select RetSeq = Row_Number() over (Order By (Select null))
                    ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
              From (Select x = Cast('<x>'+ Replace(A.Data,',','</x><x>')+'</x>' as xml).query('.')) as A 
              Cross Apply x.nodes('x') AS B(i)
             ) B


Declare @SQL varchar(max)
Select  @SQL = Stuff((Select Distinct ',' + QuoteName(ColName) From #Temp For XML Path('')),1,1,'')   
Select  @SQL = 'Select ID,' + @SQL + ' 
                From (Select ID,ColName,RetVal From #Temp) A
                Pivot (max(RetVal) For ColName in (' + @SQL + ') ) p'

Exec(@SQL);

Returns

ID  Data1   Data2   Data3
1   abc     def     NULL
2   abc     def     NULL
3   NULL    def     ghi