SQLServer-同一列上有多个PIVOT [英] SQLServer - Multiple PIVOT on same columns

问题描述

我很好奇我是否可以像这样在sql服务器的同一列上执行多个PIVOT:

I'm curious to know if I can perform multiple PIVOT on same column in sql server like this:

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(如上面的示例中所示)

For the column PLANED it works well but I am unable to add the second column ACTUAL (as demonstrated in the example above ↑)

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() ...)]

There are few limitations with PIVOT keyword and one of them has been discussed here i.e. Multiple aggregation in PIVOT is not supported by SQL-server - commented by Giorgos [something like PIVOT (min(), max(), sum() ...)]

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

There are 3 alternative to cope with this situation:

1. 具有带PIVOT关键字的case语句的一般解决方案(如上面的第一个示例所示)
2. 先取消数据透视，然后对其进行透视(在聚合之前增加行并合并列)[@bluefeet回答]
3. 复制该列以进行第二次聚合[由@NoDisplayName回答]

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

After learning about it i think the title of this question should be: Multiple aggregates in single PIVOT() rather then the existing one

推荐答案

要旋转multiple column，您需要使用Muliple Pivot's而不是multiple Aggregates.试试这个.

To pivot multiple column you need to useMuliple Pivot's not multiple Aggregates. Try this.

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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！