求一个比较复杂的转换问题

--这个是表结构和数据
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

[其他解释]
该回复于2012-10-21 00:47:25被版主删除