SQLServer-同一列上有多个PIVOT

我很好奇我是否可以在sql服务器的同一列上执行多个PIVOT，如下所示:

WITH T(ID, NAME, MSNAME, PLANED, ACTUAL)
AS (
    SELECT 1, '45rpm', 'Raised to Supplier', '2014-12-17', '2015-12-17' UNION ALL
    SELECT 1, '45rpm', 'Base Test Date', '2014-12-18', '2015-12-18' UNION ALL
    SELECT 1, '45rpm', 'Washing Approval', '2014-12-19', '2015-12-19'
)
SELECT ID, NAME
  , MAX(CASE WHEN MSNAME LIKE 'Raised to Supplier' THEN PLANED END) AS 'Raised to Supplier (PLANED)'
  , MAX(CASE WHEN MSNAME LIKE 'Base Test Date' THEN PLANED END) AS 'Base Test Date (PLANED)'
  , MAX(CASE WHEN MSNAME LIKE 'Washing Approval' THEN PLANED END) AS 'Washing Approval (PLANED)'
  , MAX(CASE WHEN MSNAME LIKE 'Raised to Supplier' THEN ACTUAL END) AS 'Raised to Supplier (ACTUAL)'
  , MAX(CASE WHEN MSNAME LIKE 'Base Test Date' THEN ACTUAL END) AS 'Base Test Date (ACTUAL)'
  , MAX(CASE WHEN MSNAME LIKE 'Washing Approval' THEN ACTUAL END) AS 'Washing Approval (ACTUAL)'
FROM T
GROUP BY ID, NAME

对于PLANED列，它可以很好地工作，但是我无法添加第二列ACTUAL(如上面的示例中所示↑)

WITH T(ID, NAME, MSNAME, PLANED, ACTUAL)
AS (
    SELECT 1, '45rpm', 'Raised to Supplier', '2014-12-17', '2015-12-17' UNION ALL
    SELECT 1, '45rpm', 'Base Test Date', '2014-12-18', '2015-12-18' UNION ALL
    SELECT 1, '45rpm', 'Washing Approval', '2014-12-19', '2015-12-19'
)
SELECT ID, NAME
  , MAX([Raised to Supplier]) AS 'Raised to Supplier (PLANED)'
  , MAX([Base Test Date]) AS 'Base Test Date (PLANED)'
  , MAX([Washing Approval]) AS 'Washing Approval (PLANED)' FROM T
PIVOT
(
    max(PLANED)
    FOR MSNAME IN ([Raised to Supplier],[Base Test Date],[Washing Approval])
) AS p1
GROUP BY ID, NAME

编辑:

PIVOT关键字几乎没有限制，此处已讨论了其中之一，即SQL Server不支持PIVOT中的多重聚合-Giorgos评论了[类似PIVOT(min()，max()，sum()... )]

有3种应对这种情况的替代方法:

带有case语句而不带PIVOT关键字的

一般解决方案(如上面的第一个示例所示)

首先取消数据透视，然后对其进行透视(在聚合之前增加行并合并列)[@bluefeet回答]

复制该列以进行第二次聚合[由@NoDisplayName回答]

在了解了这一点之后，我认为这个问题的标题应该是:在单个PIVOT()中的多个聚合，而不是现有的多个

最佳答案

要透视multiple column，您需要使用Muliple Pivot's而不是multiple Aggregates。试试这个。

SELECT ID,
       NAME,
       Max([Raised to Supplier(PLANED)])[Raised to Supplier(PLANED)],
       Max([Base Test Date(PLANED)])[Base Test Date(PLANED)],
       Max([Washing Approval(PLANED)])[Washing Approval(PLANED)],
       Max([Raised to Supplier(ACTUAL)])[Raised to Supplier(ACTUAL)],
       Max([Base Test Date(ACTUAL)])[Base Test Date(ACTUAL)],
       Max([Washing Approval(ACTUAL)])[Washing Approval(ACTUAL)]
FROM   (SELECT 1 ID,'45rpm' NAME,'Raised to Supplier' + '(PLANED)' MSNAME_pl,'Raised to Supplier' + '(ACTUAL)' MSNAME_ac,'2014-12-17' PLANED,'2015-12-17' ACTUAL
        UNION ALL
        SELECT 1,'45rpm','Base Test Date' + '(PLANED)','Base Test Date' + '(ACTUAL)','2014-12-18','2015-12-18'
        UNION ALL
        SELECT 1,'45rpm','Washing Approval' + '(PLANED)','Washing Approval' + '(ACTUAL)','2014-12-19','2015-12-19') a
       PIVOT ( Max(PLANED)
             FOR MSNAME_pl IN ([Raised to Supplier(PLANED)],
                               [Base Test Date(PLANED)],
                               [Washing Approval(PLANED)]) ) AS p1

        PIVOT ( MAX(ACTUAL)
              FOR MSNAME_ac IN ([Raised to Supplier(ACTUAL)],
                                [Base Test Date(ACTUAL)],
                                [Washing Approval(ACTUAL)])) p2
GROUP BY ID, NAME

关于SQLServer-同一列上有多个PIVOT，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27707421/

pivot

SQLServer-同一列上有多个PIVOT