在SQL中拆分混合和特殊逗号分隔值的字符串？

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

如何在SQL Server 2012中为这样的表拆分字符串:

| 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.

我的目标是获得所有abc的| def的| ghi是他们自己的专栏。

1 个解决方案

#1

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

使用#Temp表存储已分析的结果。也许你可以迁移到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