SQL查询，它为合并多行的每个记录返回可变数量的列数

Question

我正在处理一个SQL查询，它应该返回每行具有相同或不同列数的数据

例如，我有以下来自 CTE 的数据，如下所示

 With CTE 
    As(

    select distinct
    b.Acc_Num,
a.Type
    ,a.START
     ,a.END
    ,a.USAGE
    from USAGE_DETAIL a 
    join Table2 b on b.CUSTOMER_TKN=a.CUSTOMER_TKN
    )
    Select * from CTE Order By Acc_num

结果是

Acc_Num  Type  Start     End       Usage
100       A    1/1/10    1/1/15    100.00
100       A    3/1/10    3/1/15    200.00
100       A    2/1/10    2/1/15    300.00
200       A    1/1/10    1/1/15    150.00
200       A    3/1/10    3/1/15    250.00
300       A    1/1/10    1/1/15    1000.00
300       A    3/1/10    3/1/15    2000.00

在上表中，每个Acc_Num我都有多条记录，我想以相同的Acc_num对所有记录进行分组，并根据每个acc_num的记录数创建其他列，结果应如下所示

100       A    1/1/10    1/1/15    100.00    3/1/10    3/1/15    200.00   2/1/10    2/1/15    300.00    
200       A    1/1/10    1/1/15    150.00    3/1/10    3/1/15    250.00
300       A    1/1/10    1/1/15    1000.00   3/1/10    3/1/15    2000.00

我不确定是否可以为单个数据集返回可变列数。

我可以知道解决这个问题的方法吗？

Answer 1

由于您正在旋转多个列（本系列中的3个），无论如何都需要动态SQL，因此单独使用动态SQL可能是最简单的。

DECLARE @Max INT = (SELECT COUNT(*) FROM (SELECT DISTINCT Acc_Num, Type FROM Records) T)
DECLARE @Index INT = 0, @Sql VARCHAR(MAX) = ''
SET @Sql = @Sql + 'SELECT Acc_Num, Type'
WHILE @Index < @Max BEGIN
    SET @Index = @Index + 1
    DECLARE @IndexChar VARCHAR(10) = CONVERT(VARCHAR(10), @Index)
    SET @Sql = @Sql + ',MAX(CASE WHEN RowNum = ' + @IndexChar + ' THEN [Start] ELSE NULL END) AS [Start' + @IndexChar + ']'
    SET @Sql = @Sql + ',MAX(CASE WHEN RowNum = ' + @IndexChar + ' THEN [End] ELSE NULL END) AS [End' + @IndexChar + ']'
    SET @Sql = @Sql + ',MAX(CASE WHEN RowNum = ' + @IndexChar + ' THEN [Usage] ELSE NULL END) AS [Usage' + @IndexChar + ']'
END
SET @Sql = @Sql + ' FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Acc_Num ORDER BY Start) AS RowNum FROM Records) T GROUP BY Acc_Num, Type'
EXEC(@Sql)

这导致：

Acc_Num     Type Start1     End1       Usage1                Start2     End2       Usage2                Start3     End3       Usage3
----------- ---- ---------- ---------- --------------------- ---------- ---------- --------------------- ---------- ---------- ---------------------
100         A    2010-01-01 2015-01-01 100.00                2010-02-01 2015-02-01 300.00                2010-03-01 2015-03-01 200.00
200         A    2010-01-01 2015-01-01 150.00                2010-03-01 2015-03-01 250.00                NULL       NULL       NULL
300         A    2010-01-01 2015-01-01 1000.00               2010-03-01 2015-03-01 2000.00               NULL       NULL       NULL

更新

根据您的源数据，我将只替换子查询:

DECLARE @Max INT = (SELECT COUNT(*) FROM (
    SELECT DISTINCT
        b.Acc_Num,
        a.Type
    FROM USAGE_DETAIL a
        INNER JOIN Table2 b
            ON b.CUSTOMER_TKN=a.CUSTOMER_TKN
    ) T)
DECLARE @Index INT = 0, @Sql VARCHAR(MAX) = ''
SET @Sql = @Sql + 'SELECT Acc_Num, Type'
WHILE @Index < @Max BEGIN
    SET @Index = @Index + 1
    DECLARE @IndexChar VARCHAR(10) = CONVERT(VARCHAR(10), @Index)
    SET @Sql = @Sql + ',MAX(CASE WHEN RowNum = ' + @IndexChar + ' THEN [Start] ELSE NULL END) AS [Start' + @IndexChar + ']'
    SET @Sql = @Sql + ',MAX(CASE WHEN RowNum = ' + @IndexChar + ' THEN [End] ELSE NULL END) AS [End' + @IndexChar + ']'
    SET @Sql = @Sql + ',MAX(CASE WHEN RowNum = ' + @IndexChar + ' THEN [Usage] ELSE NULL END) AS [Usage' + @IndexChar + ']'
END
SET @Sql = @Sql + ' FROM (SELECT DISTINCT b.Acc_Num, a.Type, a.[START], a.[END], a.[USAGE], ROW_NUMBER() OVER (PARTITION BY b.Acc_Num ORDER BY a.[Start]) AS RowNum FROM USAGE_DETAIL a INNER JOIN Table2 b ON b.CUSTOMER_TKN=a.CUSTOMER_TKN) T GROUP BY Acc_Num, Type'
EXEC(@Sql)