SQL 列转行

 

1. 创建临时表

CREATE TABLE #TmpTable
(
 RYear INT, --年份
 RMonth INT, --月份
 RQty INT --数据
)

 

2. 数据如下

INSERT INTO #TmpTable( RYear, RMonth, RQty)
SELECT T.RYear,T.RMonth,T.RQty FROM
(
 SELECT 2011 RYear,1 RMonth,100 RQty
 UNION ALL
 SELECT 2011 RYear,1 RMonth,100 RQty
 UNION ALL
 SELECT 2011 RYear,2 RMonth,50 RQty
 UNION ALL
 SELECT 2010 RYear,1 RMonth,150 RQty
 UNION ALL
 SELECT 2010 RYear,3 RMonth,125 RQty
 UNION ALL
 SELECT 2010 RYear,3 RMonth,125 RQty
) T

 

3. 转换的SQL

SELECT RYear,
SUM(CASE WHEN RMonth=1 THEN RQty ELSE 0 END) JanQty,
SUM(CASE WHEN RMonth=2 THEN RQty ELSE 0 END) FebQty,
SUM(CASE WHEN RMonth=3 THEN RQty ELSE 0 END) MarQty
FROM #TmpTable
GROUP BY RYear