SQLServer - Multiple PIVOT on same columns

Question

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

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

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. un-pivoting the data first and then pivot them (increase the rows and merge the columns before aggregate) [answered by @bluefeet]
3. duplicate the column for second aggregation [answered by @NoDisplayName]

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

Answer 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

Answer 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.

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:

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:

| 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:

| 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 |