求一个比较复杂的转换问题
--这个是表结构和数据
CREATE TABLE #SomeTable2
(
Company VARCHAR(3),
Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1),
Quantity DECIMAL(2,1)
)
GO
INSERT INTO #SomeTable2
(Company,Year, Quarter, Amount, Quantity)
SELECT 'ABC', 2006, 1, 1.1, 2.2 UNION ALL
SELECT 'ABC', 2006, 2, 1.2, 2.4 UNION ALL
SELECT 'ABC', 2006, 3, 1.3, 1.3 UNION ALL
SELECT 'ABC', 2006, 4, 1.4, 4.2 UNION ALL
SELECT 'ABC', 2007, 1, 2.1, 2.3 UNION ALL
SELECT 'ABC', 2007, 2, 2.2, 3.1 UNION ALL
SELECT 'ABC', 2007, 3, 2.3, 2.1 UNION ALL
SELECT 'ABC', 2007, 4, 2.4, 1.5 UNION ALL
SELECT 'ABC', 2008, 1, 1.5, 5.1 UNION ALL
SELECT 'ABC', 2008, 3, 2.3, 3.3 UNION ALL
SELECT 'ABC', 2008, 4, 1.9, 4.2 UNION ALL
SELECT 'XYZ', 2006, 1, 2.1, 3.6 UNION ALL
SELECT 'XYZ', 2006, 2, 2.2, 1.8 UNION ALL
SELECT 'XYZ', 2006, 3, 3.3, 2.6 UNION ALL
SELECT 'XYZ', 2006, 4, 2.4, 3.7 UNION ALL
SELECT 'XYZ', 2007, 1, 3.1, 1.9 UNION ALL
SELECT 'XYZ', 2007, 2, 1.2, 1.2 UNION ALL
SELECT 'XYZ', 2007, 3, 3.3, 4.2 UNION ALL
SELECT 'XYZ', 2007, 4, 1.4, 4.0 UNION ALL
SELECT 'XYZ', 2008, 1, 2.5, 3.9 UNION ALL
SELECT 'XYZ', 2008, 2, 3.5, 2.1 UNION ALL
SELECT 'XYZ', 2008, 3, 1.3, 3.9 UNION ALL
SELECT 'XYZ', 2008, 4, 3.9, 3.4
GO
--下面是转换后的结果，弄了半天没弄出来。
CompanyYearQ1AmtQ1QtyQ2AmtQ2QtyQ3AmtQ3QtyQ4AmtQ4QtyTotalAmtTotalQty
ABC20061.12.21.22.41.31.31.44.25.010.1
ABC20072.12.32.23.12.32.12.41.59.09.0
ABC20081.55.10.00.02.33.31.94.25.712.6
XYZ20062.13.62.21.83.32.62.43.710.011.7
XYZ20073.11.91.21.23.34.21.44.09.011.3
XYZ20082.53.93.52.11.33.93.93.411.213.3
SELECT amt.Company,
amt.Year,
isnull(amt.[1],0) AS Q1Amt,
isnull(qty.[1],0) AS Q1Qty,
isnull(amt.[2],0) AS Q2Amt,
isnull(qty.[2],0) AS Q2Qty,
isnull(amt.[3],0) AS Q3Amt,
isnull(qty.[3],0) AS Q3Qty,
isnull(amt.[4],0) AS Q4Amt,
isnull(qty.[4],0) AS Q4Qty,
isnull(amt.[1],0)+isnull(amt.[2],0)+isnull(amt.[3],0)+isnull(amt.[4],0) AS TotalAmt,
isnull(qty.[1],0)+isnull(qty.[2],0)+isnull(qty.[3],0)+isnull(qty.[4],0) AS TotalQty
FROM (SELECT Company, Year, Quarter, Amount FROM #SomeTable2) t1
PIVOT (SUM(Amount) FOR Quarter IN ([1], [2], [3], [4])) AS amt
INNER JOIN
(SELECT Company, Year, Quarter, Quantity FROM #SomeTable2) t2
PIVOT (SUM(Quantity) FOR Quarter IN ([1], [2], [3], [4])) AS qty
ON qty.Company = amt.Company
AND qty.Year = amt.Year
ORDER BY amt.Company, amt.Year
