做
GROUP BY LOCATION WITH ROLLUP
你需要什么?
;WITH MYTABLE AS
(
SELECT 'FL' AS LOCATION,CAST('10/1/2009' AS DATETIME) AS my_date, 1 AS QTY UNION ALL
SELECT 'MD' AS LOCATION,'10/1/2009' AS my_date, 27 AS QTY UNION ALL
SELECT 'MD' AS LOCATION,'01/1/2010' AS my_date, 1024 AS QTY UNION ALL
SELECT 'FL' AS LOCATION,'07/1/2010' AS my_date, 98 AS QTY UNION ALL
SELECT 'FL' AS LOCATION,'07/1/2010' AS my_date, 5 AS QTY
)
SELECT
COALESCE(LOCATION,'TOTAL'),
SUM(CASE WHEN my_date >= '10/1/2009' AND my_date
ELSE NULL END) AS QTR1,
SUM(CASE WHEN my_date >= '01/1/2010' AND my_date
ELSE NULL END) AS QTR2,
SUM(CASE WHEN my_date >= '04/1/2010' AND my_date
ELSE NULL END) AS QTR3,
SUM(CASE WHEN my_date >= '07/1/2010' AND my_date
ELSE NULL END) AS QTR4
FROM MYTABLE
GROUP BY LOCATION WITH ROLLUP
退换商品
QTR1 QTR2 QTR3 QTR4
----- ----------- ----------- ----------- -----------
FL 1 NULL NULL 103
MD 27 1024 NULL NULL
TOTAL 28 1024 NULL 103