SQL 如何将两列数据转换为不同的列？

Question

这是我的 table :

| Scheme Code | MonthYear | Revenue | Revenue2 |
|-------------|-----------|---------|----------|
| 18VDA       | 2018.1    | 100     | 50       |
| 18VDA       | 2018.2    | 200     | 100      |
| 18VDA       | 2018.3    | 200     | 150      |

我想把它变成这样:

| Scheme Code | 2018.1 A | 2018.2 A | 2018.3 A | 2018.1 B | 2018.2 B | 2018.3 B |
|-------------|----------|----------|----------|----------|----------|----------|
| 18VDA       | 100      | 200      | 200      | 50       | 100      | 150      |

我该怎么做才能让它在 MonthYear 中旋转，但它会为 Revenue 和 Revenue2 复制它？

谢谢

编辑:弄乱了我希望的输出表!我已经编辑了我想要查看的实际输出表!

编辑2:

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

Select @cols = STUFF((SELECT ',' + QUOTENAME([MonthYear]) 
                    from tableA
                    group by [MonthYear]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT *
             FROM ( SELECT [Scheme Code], MonthYear ,[Revenue]
                    FROM TableA
                    ) a
              PIVOT(sum(Revenue) for MonthYear in (' + @cols + ') 
                       ) as RevenueMonth
              ORDER BY [Scheme Code]'    

 execute(@query);

我写的这段代码只写一列，我得到这样的输出:

| Scheme Code | 2018.1 | 2018.2 | 2018.3 |
|-------------|--------|--------|--------|
| 18VDA       | 100    | 200    | 200    |

Answer 1

我的建议始终是先尝试将查询编写为硬编码或静态版本，然后再深入研究动态 SQL。这让您可以使用较小的数据子集获得所需的最终结果，并且您可以验证您的逻辑是否正确。

我将通过先对两个 Revenue 列执行 UNPIVOT 来解决这个问题，然后再考虑应用 PIVOT 函数。 UNPIVOT 你可以使用 UNPIVOT 函数或者你可以使用 CROSS APPLY 和 UNION ALL 来转换你的两个 Revenue 列合并为一列。查询的静态版本类似于:

select *
from
(
    select 
        t.[Scheme Code],
        new_colname = concat(t.[MonthYear], ' ', r.colname),
        r.colvalue
    from yourtable t
    cross apply
    (
        select 'A', Revenue union all
        select 'B', Revenue2
    ) r (colname, colvalue)
) d
pivot 
(
    sum(colvalue)
    for new_colname in ([2018.1 A], [2018.2 A], [2018.3 A], [2018.1 B], [2018.2 B], [2018.3 B])
) p;

您会注意到，在 CROSS APPLY 中，我添加了一个带有 A 或 B 的列，用于标识 Revenue 或 Revenue2 列。然后使用它为 PIVOT 创建新的列名。

这应该会产生你想要的结果。现在要动态地执行此操作，您只需要将 SQL 转换为动态代码。您可以使用以下方法获得结果:

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

Select @cols = STUFF((SELECT ',' + QUOTENAME(concat([MonthYear], x.col)) 
                    from yourtable
                    cross join (select col = ' A' union all select ' B') x
                    group by [MonthYear], x.col
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT *
             FROM 
             ( 
                select 
                    t.[Scheme Code],
                    new_colname = concat(t.[MonthYear], '' '', r.colname),
                    r.colvalue
                from yourtable t
                cross apply
                (
                    select ''A'', Revenue union all
                    select ''B'', Revenue2
                ) r (colname, colvalue)
              ) a
              PIVOT
              (
                sum(colvalue) for new_colname in (' + @cols + ') 
              ) as x
              ORDER BY [Scheme Code]';

exec sp_executesql @query;

这两个应该产生相同的结果 (dbfiddle demo)