SQLServer 使用 PIVOT
单个透视
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)]
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
GROUP BY ID, NAME
多个透视
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