有一張表兩個字段：_date，_num，
現在要產生如下的表格，請設計相關SQL語句。
年份
1月
2月
3月
4月
5月
6月
7月
8月
9月
10月
11月
12月
2010
數量
數量
數量
數量
數量
數量
數量
數量
數量
數量
數量
數量
2011
數量
數量
數量
數量
數量
數量
數量
數量
數量
數量
數量
數量
……
sql code
--創建測試表
CREATE TABLE Test
(
_date DATETIME,
_num INT
)
--插入測試數據
INSERT dbo.Test
SELECT '2010-01-01',100 UNION ALL
SELECT '2010-02-01',100 UNION ALL
SELECT '2010-03-01',100 UNION ALL
SELECT '2010-04-01',200 UNION ALL
SELECT '2010-05-01',300 UNION ALL
SELECT '2010-06-01',400
SELECT * FROM dbo.Test
--創建臨時表
CREATE TABLE #table
(
_year INT,
_month INT,
_num INT
)
--為臨時表插入數據
INSERT #table
SELECT
YEAR(t.[_date]),
MONTH(t.[_date]),
SUM(t._num)
FROM dbo.Test t
GROUP BY YEAR(t.[_date]),MONTH(t.[_date])
--查詢結果如下
SELECT t.[_year] '年份',
SUM(CASE t.[_month] WHEN 1 THEN t.[_num] ELSE 0 END) AS '1月',
SUM(CASE t.[_month] WHEN 2 THEN t.[_num] ELSE 0 END) AS '2月',
SUM(CASE t.[_month] WHEN 3 THEN t.[_num] ELSE 0 END) AS '3月',
SUM(CASE t.[_month] WHEN 4 THEN t.[_num] ELSE 0 END) AS '4月',
SUM(CASE t.[_month] WHEN 5 THEN t.[_num] ELSE 0 END) AS '5月',
SUM(CASE t.[_month] WHEN 6 THEN t.[_num] ELSE 0 END) AS '6月',
SUM(CASE t.[_month] WHEN 7 THEN t.[_num] ELSE 0 END) AS '7月',
SUM(CASE t.[_month] WHEN 8 THEN t.[_num] ELSE 0 END) AS '8月',
SUM(CASE t.[_month] WHEN 9 THEN t.[_num] ELSE 0 END) AS '9月',
SUM(CASE t.[_month] WHEN 10 THEN t.[_num] ELSE 0 END) AS '10月',
SUM(CASE t.[_month] WHEN 11 THEN t.[_num] ELSE 0 END) AS '11月',
SUM(CASE t.[_month] WHEN 12 THEN t.[_num] ELSE 0 END) AS '12月'
FROM #table t GROUP BY t.[_year]
--刪除臨時表
DROP TABLE #table
經過研究，其實可以更簡單，採用數據透視方法。
code如下：
declare @sql varchar(max)
set @sql = 'select year(_date) [年份]'
select @sql = @sql + ',sum(case month(_date) when ''' + ltrim(_date) + ''' then _num else 0 end) [' + ltrim(_date) + '月]'
from (select month(_date) _date from dbo.Test group by month(_date)) t
select @sql = @sql + ' from Test group by year(_date)'
exec(@sql)
