什么类型的TSQL语句可以用于从表1(下面(中的数据读取并生成预期结果(下面(:
表1中有以下数据:
ID Field1 Field2 Field3
----------------------------------------------------------------
1 R1F1 R1F2 R1F3, R1F4, R1F5, R1F6
2 R2F1 R2F2 R2F3, R2F4, R2F5, R2F6
预期结果将产生以下结果的SQL语句:
ID Field1 Field2 Col1 Col2 Col3 Col4
----------------------------------------------------------------
1 R1F1 R1F2 R1F3 R1F4 R1F5 R1F6
2 R2F1 R2F2 R2F3 R2F4 R2F5 R2F6
您也可以使用parsename
(仅限于4个部分(或string_Split
,如下所示:
Select t.Field1, t.Field2
,Col1 = parsename(B.NewStr,4)
,Col2 = parsename(B.NewStr,3)
,Col3 = parsename(B.NewStr,2)
,Col4 = parsename(B.NewStr,1)
From @t t
Cross Apply (values (replace(Field3,',','.') ) ) B(NewStr)
Metohd2:
;WITH C AS(
SELECT t.Field1, t.Field2
,value
,ROW_NUMBER() OVER(PARTITION BY t.Field3 ORDER BY (SELECT NULL)) as rn
FROM @t t
CROSS APPLY STRING_SPLIT(Field3, ',') AS SS
)
SELECT Field1, Field2
,[1] AS Col1
,[2] AS Col2
,[3] AS Col3
,[4] AS Col4
FROM C
PIVOT(
MAX(VALUE)
FOR RN IN([1],[2],[3],[4])
) as PVT
请查找数据库<gt;小提琴在这里。