SQLServer——同一列上的多个PIVOT

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

我很想知道我是否可以在sql server的同一列上执行多个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

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

EDIT:

编辑:

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

PIVOT关键字几乎没有什么限制，这里讨论了其中的一个限制，即使用PIVOT的多个聚合不受SQL-server的支持——Giorgos注释[比如PIVOT (min()、max()、sum()…]]

There are 3 alternative to cope with this situation:

应对这种情况有三种选择:

1. general solution with case statement without PIVOT keyword (as show in first example above)
2. 不带PIVOT关键字的case语句的通用解决方案(如上面的第一个例子所示)
3. un-pivoting the data first and then pivot them (increase the rows and merge the columns before aggregate) [answered by @bluefeet]
4. 先不旋转数据，然后再旋转它们(在聚合之前增加行并合并列)[@bluefeet回复]
5. duplicate the column for second aggregation [answered by @NoDisplayName]
6. 复制列用于第二个聚合[由@NoDisplayName回答]

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

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

2 个解决方案

#1

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

#2

SQL Server doesn't allow for pivoting on multiple columns, I'd actually suggest looking at unpivoting the Planed and Actual column first, then pivot the data into the final result that you want.

SQL Server不允许对多个列进行旋转，我实际上建议首先对计划和实际的列进行反旋转，然后将数据透视为您想要的最终结果。

You didn't specify what version of SQL Server you are using, but you can use the UNPIVOT function or you can use CROSS APPLY to convert the multiple columns into multiple rows. The basic syntax will be:

您没有指定正在使用的SQL Server的版本，但是可以使用UNPIVOT函数，或者使用CROSS APPLY将多个列转换为多个行。基本语法是:

select 
  id,
  name, 
  new_col = msname + '_' + col,
  value
from t
cross apply
(
  select 'planed', planed union all
  select 'actual', actual
) c (col, value);

See SQL Fiddle with Demo. This gets your data into the format:

参见SQL小提琴演示。这将使您的数据进入格式:

| ID |  NAME |                   NEW_COL |      VALUE |
|----|-------|---------------------------|------------|
|  1 | 45rpm | Raised to Supplier_planed | 2014-12-17 |
|  1 | 45rpm | Raised to Supplier_actual | 2015-12-17 |
|  1 | 45rpm |     Base Test Date_planed | 2014-12-18 |
|  1 | 45rpm |     Base Test Date_actual | 2015-12-18 |
|  1 | 45rpm |   Washing Approval_planed | 2014-12-19 |
|  1 | 45rpm |   Washing Approval_actual | 2015-12-19 |

You now have the new column names that you want as well as the values in a single column, instead of multiple columns. Now you can pivot the data into the final result:

现在，您有了想要的新列名以及单个列中的值，而不是多个列。现在您可以将数据透视到最终结果:

select id, name, 
  [Raised to Supplier_planed], [Raised to Supplier_actual],
  [Base Test Date_planed], [Base Test Date_actual],
  [Washing Approval_planed], [Washing Approval_actual]
from
(
  select 
    id,
    name, 
    new_col = msname + '_' + col,
    value
  from t
  cross apply
  (
    select 'planed', planed union all
    select 'actual', actual
  ) c (col, value)
) d
pivot
(
  max(value)
  for new_col in ([Raised to Supplier_planed], [Raised to Supplier_actual],
                  [Base Test Date_planed], [Base Test Date_actual],
                  [Washing Approval_planed], [Washing Approval_actual])
) p;

See SQL Fiddle with Demo. This gives the same final result:

参见SQL小提琴演示。这给出了相同的最终结果:

| ID |  NAME | RAISED TO SUPPLIER_PLANED | RAISED TO SUPPLIER_ACTUAL | BASE TEST DATE_PLANED | BASE TEST DATE_ACTUAL | WASHING APPROVAL_PLANED | WASHING APPROVAL_ACTUAL |
|----|-------|---------------------------|---------------------------|-----------------------|-----------------------|-------------------------|-------------------------|
|  1 | 45rpm |                2014-12-17 |                2015-12-17 |            2014-12-18 |            2015-12-18 |              2014-12-19 |              2015-12-19 |