主页 / dba / 问题 / 273638
Accepted
Mike S
Asked: 2020-08-13 05:31:17 +0800 CST

如何将多个不同大小的分隔字符串拆分为行？

  • 772

我有一种情况，对于我的表中的每个 ID，都有多个字段，每个字段都包含一个逗号分隔的字符串。字符串不一定具有彼此相同数量的元素。数据的格式来自另一个团队，我无法更改。

如何使用 SQL Server 2016 将这些字符串拆分为行？如果可以避免，我宁愿不向数据库添加函数。

这是我开始的示例以及我希望实现的目标：

DECLARE @SourceTable TABLE (ID INT, String1 VARCHAR(100), 
  String2 VARCHAR(100), String3 VARCHAR(100))

INSERT INTO @SourceTable (ID, String1, String2, String3)
VALUES (1, 'a1,a2,a3', 'b1,b2,b3,b4,b5,b6', 'c1,c2'),
       (2, 'd1,d2', 'e1', 'f1,f2')

--Source
SELECT *
FROM @SourceTable

期望的输出：

ID  Position    S1_Transformed  S2_Transformed  S3_Transformed
--  --------    --------------  --------------  --------------
1   1           a1              b1              c1
1   2           a2              b2              c2
1   3           a3              b3  
1   4                           b4  
1   5                           b5  
1   6                           b6  
2   1           d1              e1              f1
2   2           d2                              f2

编辑：根据 Aaron 的评论更正了输出。

sql-server sql-server-2016

1 个回答

  1. Best Answer

    如果您可以忍受拥有一个函数来提供尚未（尚未）内置于引擎中的功能，则可以创建此函数，该函数在每个值旁边返回一个序数位置（有许多不同的方法来编写此拆分函数）：

    CREATE FUNCTION dbo.SplitCSVInOrder
(
    @List  nvarchar(max)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN
    (
      WITH L0(c) AS (SELECT 1 UNION ALL SELECT 1),
           L1(c) AS (SELECT 1 FROM L0, L0 AS B),
           L2(c) AS (SELECT 1 FROM L1, L1 AS B),
           L3(c) AS (SELECT 1 FROM L2, L2 AS B),
           L4(c) AS (SELECT 1 FROM L3, L3 AS B),
           L5(c) AS (SELECT 1 FROM L4, L4 AS B),
           N (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY c) FROM L5)
        SELECT 
           [Position] = ROW_NUMBER() OVER (ORDER BY n),
           [Value] = LTRIM(RTRIM(SUBSTRING(@List, n,
                CHARINDEX(',', @List + ',', n) - n)))
        FROM N WHERE n <= LEN(@List)
            AND SUBSTRING(',' + @List, n, 1) = ','
    );

    然后你可以 unpivot / re-pivot 排列每个位置的值：

    DECLARE @SourceTable TABLE
( 
  ID INT, 
  String1 varchar(100), 
  String2 varchar(100), 
  String3 varchar(100)
);

INSERT INTO @SourceTable (ID, String1, String2, String3)
VALUES (1, 'a1,a2,a3', 'b1,b2,b3,b4,b5,b6', 'c1,c2'),
       (2, 'd1,d2', 'e1', 'f1,f2');

;WITH x AS 
(
  SELECT * FROM @SourceTable
    UNPIVOT (s FOR strings IN (String1, String2, String3)) AS up
    CROSS APPLY dbo.SplitCSVInOrder(s)
),
p AS 
(
  SELECT * FROM x PIVOT (MAX(Value) 
    FOR strings IN ([String1],[String2],[String3])) AS p
)
SELECT ID, Position, S1_Transformed = COALESCE(MIN(String1),''), 
                     S2_Transformed = COALESCE(MIN(String2),''), 
                     S3_Transformed = COALESCE(MIN(String3),'')
FROM p
GROUP BY ID, Position
ORDER BY ID, Position;

    结果：

    ID  Position    S1_Transformed  S2_Transformed  S3_Transformed
--  --------    --------------  --------------  --------------
1   1           a1              b1              c1
1   2           a2              b2              c2
1   3           a3              b3  
1   4                           b4  
1   5                           b5  
1   6                           b6  
2   1           d1              e1              f1
2   2           d2                              f2

    我确信有一种更简单的方法可以做到这一点，但这就是我提出的主要在 SQL-Server-2005 上提出的大脑想出的方法。

    您是否希望该查询更容易制定？我也是！一方面，如果有一个本地字符串拆分函数也返回序数位置，那就太好了。这将不需要一个函数来确保输出的输出顺序与字符串中列出的顺序相同。我已经请求大家帮助微软在 SQL Server 的下一个版本中添加一个函数。请投票并添加建设性评论，在此处解释您的业务案例：

    并查看我在这里特别要求的一些背景：

    • 1

相关问题