如何在multipale列中拆分列值

debugcn 发表于 Dev

古里香卡

你能告诉我如何潜水吗

ID     SN    Types
1      123   ABC,XYZ,TEST, RJK,CDF,TTT,UMB,UVX
2      234   RJK,CDF,TTT,UMB,UVX,TTT,UMB,UVX
3      345   OID,XYZ,TTT,UMB,UVX,TTT,UMB,UVX

作为

 ID     SN    Types1  Types2  Types3  Types4  Types5  Types6  Types7  Types8
 1      123   ABC       XYZ    TEST     RJK    CDF     TTT     UMB     UVX  
 2      234   RJK       CDF    TTT      UMB    UVX     TTT     UMB     UVX 
 3      345   OID       XYZ    TTT      UMB    UVX     TTT     UMB     UVX

请给我发送sql代码thanx，

亚伦·伯特兰（Aaron Bertrand）

首先，创建一个维护顺序的拆分函数：

CREATE FUNCTION [dbo].[SplitStrings_Ordered]
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (SELECT [Index] = ROW_NUMBER() OVER (ORDER BY Number), Item 
    FROM (SELECT Number, Item = SUBSTRING(@List, Number, 
      CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)
     FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
      FROM sys.all_objects) AS n(Number)
      WHERE Number <= CONVERT(INT, LEN(@List))
      AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
    ) AS y);

现在，您可以将此功能与结合使用PIVOT。这是一个自封闭的示例，但是您可以@d在最后一个查询中用您的实际表替换。

DECLARE @d TABLE(ID INT, SN INT, Types NVARCHAR(MAX));

INSERT @d VALUES
(1,123,N'ABC,XYZ,TEST, RJK,CDF,TTT,UMB,UVX'),
(2,234,N'RJK,CDF,TTT,UMB,UVX,TTT,UMB,UVX'  ),
(3,345,N'OID,XYZ,TTT,UMB,UVX,TTT,UMB,UVX'  );

SELECT ID,SN,
 Types1 = [1], Types2 = [2], Types3 = [3], Types4 = [4],
 Types1 = [5], Types6 = [6], Types7 = [7], Types8 = [8]
FROM @d AS d CROSS APPLY dbo.SplitStrings_Ordered(d.Types, ',') AS y
PIVOT (MAX(Item) FOR [Index] IN ([1],[2],[3],[4],[5],[6],[7],[8])) AS p;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。