sql - 逗号连接列的 tSQL UNPIVOT 成多行

Question

我有一个包含值列的表。该值可以是一个值，也可以是用逗号分隔的多个值:

id | assess_id | question_key | item_value
---+-----------+--------------+-----------
 1 |       859 |     Cust_A_1 |        1,5
 2 |       859 |     Cust_B_1 |          2

我需要根据 item_value 逆透视数据，使其看起来像这样:

id | assess_id | question_key | item_value
---+-----------+--------------+-----------
 1 |       859 |     Cust_A_1 |          1
 1 |       859 |     Cust_A_1 |          5
 2 |       859 |     Cust_B_1 |          2

如何在 SQL Server 2012 上的 tSQL 中做到这一点？

Answer 1

我们有一个用户定义的函数，我们将其用于我们称为“split_delimiter”的东西:

CREATE FUNCTION [dbo].[split_delimiter](@delimited_string VARCHAR(8000), @delimiter_type CHAR(1))
RETURNS TABLE AS
RETURN
WITH cte10(num) AS 
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)                         
,cte100(num) AS 
(
    SELECT  1 
    FROM    cte10 t1, cte10 t2
)
,cte10000(num) AS 
(
    SELECT  1 
    FROM    cte100 t1, cte100 t2
)
,cte1(num) AS 
(
    SELECT  TOP (ISNULL(DATALENGTH(@delimited_string),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
    FROM    cte10000
)
,cte2(num) AS 
(
    SELECT  1 
    UNION ALL
    SELECT  t.num+1 
    FROM    cte1 t
    WHERE   SUBSTRING(@delimited_string,t.num,1) = @delimiter_type
)
,cte3(num,[len]) AS
(
    SELECT  t.num
            ,ISNULL(NULLIF(CHARINDEX(@delimiter_type,@delimited_string,t.num),0)-t.num,8000)
    FROM    cte2 t
)

SELECT  delimited_item_num    = ROW_NUMBER() OVER(ORDER BY t.num)
        ,delimited_value    = SUBSTRING(@delimited_string, t.num, t.[len])
FROM    cte3 t;
GO

它将采用最多 8000 个字符的 varchar 值，并将返回一个表格，其中分隔元素被分成几行。在您的示例中，您需要使用外部应用将这些分隔值转换为单独的行:

SELECT  my_table.id, my_table.assess_id, question_key, my_table.delimited_items.item_value
FROM    my_table
OUTER APPLY(
    SELECT  delimited_value AS item_value
    FROM    my_database.dbo.split_delimiter(my_table.item_value, ',')
    ) AS delimited_items