複数の要約列を行レコードmssqlに変換

--1.             。
/*--
	       Quarter    ，    （ Year）   （ Quantity）  
	    （ Price）。            ，               float
	     CAST        decimal       。
--*/
DECLARE @t TABLE(Year int,Quarter int,Quantity decimal(10,1),Price decimal(10,2))
INSERT @t SELECT 1990, 1, 1.1, 2.5
UNION ALL SELECT 1990, 1, 1.2, 3.0
UNION ALL SELECT 1990, 2, 1.2, 3.0
UNION ALL SELECT 1990, 1, 1.3, 3.5
UNION ALL SELECT 1990, 2, 1.4, 4.0
UNION ALL SELECT 1991, 1, 2.1, 4.5
UNION ALL SELECT 1991, 2, 2.1, 4.5
UNION ALL SELECT 1991, 2, 2.2, 5.0
UNION ALL SELECT 1991, 1, 2.3, 5.5
UNION ALL SELECT 1991, 1, 2.4, 6.0

--    
SELECT Year,
	Q1_Amount=SUM(CASE Quarter WHEN 1 THEN Quantity END),
	Q1_Price=CAST(AVG(CASE Quarter WHEN 1 THEN Price END) AS DECIMAL(10,2)),
	Q1_Money=CAST(SUM(CASE Quarter WHEN 1 THEN Quantity*Price END) AS DECIMAL(10,2)),
	Q2_Amount=SUM(CASE Quarter WHEN 2 THEN Quantity END),
	Q2_Price=CAST(AVG(CASE Quarter WHEN 2 THEN Price END) AS DECIMAL(10,2)),
	Q2_Money=CAST(SUM(CASE Quarter WHEN 2 THEN Quantity*Price END) AS DECIMAL(10,2))
FROM @t
GROUP BY Year
/*--  
Year  Q1_Amount  Q1_Price   Q1_Money  Q2_Amount  Q2_Price  Q2_Money
------- ----------------- --------------- ----------------- ----------------- -------------- ----------------
1990  3.6         3.00       10.90       2.6          3.50      9.20
1991  6.8         5.33       36.50       4.3          4.75      20.45
--*/


/*=====================================================*/



--2.          。
/*--
	      ，   Groups  ，     Item、Color Quantity    
	    Item Color         。
--*/

DECLARE @t TABLE(Groups char(2),Item varchar(10),Color varchar(10),Quantity int)
INSERT @t SELECT 'aa','Table','Blue', 124
UNION ALL SELECT 'bb','Table','Red',  -23
UNION ALL SELECT 'bb','Cup'  ,'Green',-23
UNION ALL SELECT 'aa','Chair','Blue', 101
UNION ALL SELECT 'aa','Chair','Red',  -90

--    
SELECT Groups,
	[Table]=SUM(CASE Item WHEN 'Table' THEN Quantity END),
	[Cup]=SUM(CASE Item WHEN 'Cup' THEN Quantity END),
	[Chair]=SUM(CASE Item WHEN 'Chair' THEN Quantity END),
	[Blue]=SUM(CASE Color WHEN 'Blue' THEN Quantity END),
	[Red]=SUM(CASE Color WHEN 'Red' THEN Quantity END),
	[Green]=SUM(CASE Color WHEN 'Green' THEN Quantity END)
FROM @t
GROUP BY Groups
/*--  
Groups Table       Cup       Chair        Blue       Red       Green 
--------- --------------- ---------------- ----------------- --------------- ---------------- --------------- 
aa     124       NULL      11          225        -90         NULL
bb     -23       -23         NULL      NULL      -23         -23
--*/