值列和位列上的SQL枢轴

Question

我有一个这样的RawDataTable:

<表类> 日期 国家 价值准确tbody><<tr>2000ABW4对2001ABW9对2002ABW8对2000GBR1假2001GBR3对2002GBR4假

我通常发现使用条件大小写表达式进行简单的透视要容易得多，比如这样，您可以轻松地剪切和粘贴重复列。Pivot只是不直观的语法糖，SQL Server内部扩展为大小写表达式。

select date,
Max(case when country='ABW' then value end) ABW,
Max(case when country='GBR' then value end) GBR,
Max(case when country='ABW' then accurate end) ABWAccurate,
Max(case when country='GBR' then accurate end) GBRAccurate
from t
group by date

下面是使用pivot的解决方案:

sqlfiddle: multipivot

;WITH PIV1 AS
(
SELECT * FROM (
SELECT    
[Date],
[Country],
[Value]
FROM RawDataTable
) CountryVals
PIVOT (
SUM([Value])
FOR [Country]
IN ([ABW],[GBR])
) AS PivotTable
), PIV2 AS
(
SELECT * FROM (
SELECT    
[Date],
[Country],
[Accurate]
FROM RawDataTable
) CountryVals
PIVOT (
MAX([Accurate])
FOR [Country]
IN ([ABW],[GBR])
) AS PivotTable
)

SELECT PIV1.Date, PIV1.ABW, PIV1.GBR, PIV2.ABW AS [ABW Accurate] , PIV2.GBR [GBR Accurate]
FROM PIV1 INNER JOIN PIV2 ON PIV1.[Date] = PIV2.[Date]