クロス集計クエリー

CREATE TABLE Pivot ( Year SMALLINT,

 Quarter   TINYINT, 
 Amount      DECIMAL(2,1) )

GO INSERT INTO Pivot VALUES (1990, 1, 1.1) INSERT INTO Pivot VALUES (1990, 2, 1.2) INSERT INTO Pivot VALUES (1990, 3, 1.3) INSERT INTO Pivot VALUES (1990, 4, 1.4) INSERT INTO Pivot VALUES (1991, 1, 2.1) INSERT INTO Pivot VALUES (1991, 2, 2.2) INSERT INTO Pivot VALUES (1991, 3, 2.3) INSERT INTO Pivot VALUES (1991, 4, 2.4) GO

SELECT Year,

   SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
   SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
   SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
   SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4

FROM Pivot GROUP BY Year GO

SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal? FROM (SELECT Year,

            SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
            SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
            SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
            SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
    FROM Pivot AS P
    GROUP BY P.Year) AS P1

GO

Year Q1 Q2 Q3 Q4

1990 1.1 1.2 1.3 1.4 1991 2.1 2.2 2.3 2.4

(2 件処理されました)

Year Q1 Q2 Q3 Q4 YearTotal?

1990 1.1 1.2 1.3 1.4 5.0 1991 2.1 2.2 2.3 2.4 9.0

(2 件処理されました)