将行转换为考虑多列的列

Question

我有当前的基础表：

我需要将其转换为：

< img src =“ https://i.sstatic.net/0caad.png” alt =“在此处输入图像说明”>

我已经有一个动态SQL，在LTD值下面为我提供了下面的结果：

，

但我无法基于列有限公司和男孩动态创建聚合名称的列，并将邮政修复设置为列名称。

在下面找到我的SQL：

create table #tempIE
(
    AGGREGATIONNAME varchar(2),
    LTD decimal,
       BOY decimal,
       MONTH int
)

insert into #tempIE values ('XX', 50, 45, 00)
insert into #tempIE values ('XX', 150, 145, 01)
insert into #tempIE values ('XX', 300, 295, 02)

insert into #tempIE values ('YY', 25, 20, 00)
insert into #tempIE values ('YY', 50, 45, 01)
insert into #tempIE values ('YY', 75, 70, 02)

insert into #tempIE values ('ZZ', 500, 495, 00)
insert into #tempIE values ('ZZ', 600, 595, 01)
insert into #tempIE values ('ZZ', 700, 695, 02)

SELECT *
FROM #tempIE



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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.AGGREGATIONNAME) 
            FROM #tempIE c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT MONTH, ' + @cols + ' INTO ##tempIEnew from 
            (
                select AGGREGATIONNAME
                    , LTD
                                 --, BOY
                    , MONTH
                from #tempIE
           ) x
            pivot 
            (
                 max(LTD)
                for AGGREGATIONNAME in (' + @cols + ')
            ) p '
                    ;

execute(@query)



SELECT *
FROM ##tempIEnew

drop table #tempIE
drop table ##tempIEnew

Answer 1

正如我在评论中提到的那样，我更喜欢使用条件汇总而不是pivot运算符，因此可以更容易使用，因为它没有限制性的语法。

通过有条件的聚合，您可以轻松地实现以下静态枢轴：

SELECT IE.[MONTH],
       MAX(CASE IE.AGGREGATIONNAME WHEN 'XX' THEN IE.LTD END) AS XX_LTD,
       MAX(CASE IE.AGGREGATIONNAME WHEN 'YY' THEN IE.LTD END) AS YY_LTD,
       /* ... */
       MAX(CASE IE.AGGREGATIONNAME WHEN 'ZZ' THEN IE.BOY END) AS ZZ_BPY
FROM #tempIE IE
GROUP BY IE.[MONTH];

对于动态枢轴，然后可以通过以下内容实现此目标。

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

DECLARE @Delim nvarchar(3) = N',' + @CRLF

SELECT @SQL = N'SELECT IE.[MONTH],' + @CRLF +
              STRING_AGG(N'       MAX(CASE IE.AGGREGATIONNAME WHEN ' + QUOTENAME(IE.AGGREGATIONNAME,'''') + N' THEN IE.LTD END) AS ' + QUOTENAME(CONCAT(IE.AGGREGATIONNAME,'_LTD')),@Delim) WITHIN GROUP (ORDER BY IE.AGGREGATIONNAME) + N',' + @CRLF +
              STRING_AGG(N'       MAX(CASE IE.AGGREGATIONNAME WHEN ' + QUOTENAME(IE.AGGREGATIONNAME,'''') + N' THEN IE.BOY END) AS ' + QUOTENAME(CONCAT(IE.AGGREGATIONNAME,'_BOY')),@Delim) WITHIN GROUP (ORDER BY IE.AGGREGATIONNAME) + @CRLF +
              N'FROM #tempIE IE' + @CRLF +
              N'GROUP BY IE.[MONTH];'
FROM (SELECT DISTINCT AGGREGATIONNAME
      FROM #tempIE) IE;

--PRINT @SQL;--Your best friend

EXEC sys.sp_executesql @SQL;

请注意，我假设您使用的是SQL Server的完全支持版本（虽然没有注意到您不是），因此无需为XML Path 和2个通过。我还切换到创建查询内联，因为尝试使用变量@columns尝试使用该查询，这是一个真正的痛苦，有条件聚合。实际上，我不建议该方法各自导致（例如Pivot）。