SQL 行转列

 

1. 创建临时表

CREATE TABLE #TmpTable
(
 RYear INT, --年份
 JQty INT, --一月的数据
 FQty INT, --二月的数据
 MQty INT, --三月的数据
 AQty INT --四月的数据
)

 

2. 数据如下

INSERT INTO #TmpTable( RYear, JQty, FQty, MQty, AQty )
SELECT T.RYear,T.JQty,T.FQty,T.MQty,T.AQty FROM
(
 SELECT 2011 RYear,1 JQty,2 FQty,3 MQty,4 AQty
 UNION ALL
 SELECT 2011 RYear,1 JQty,2 FQty,3 MQty,4 AQty
 UNION ALL
 SELECT 2010 RYear,1 JQty,2 FQty,3 MQty,4 AQty
 UNION ALL
 SELECT 2010 RYear,1 JQty,2 FQty,3 MQty,4 AQty
) T

 

3. 转换的SQL

SELECT RYear,'Jan' RMonth,SUM(JQty) Qty FROM #TmpTable GROUP BY RYear
UNION ALL
SELECT RYear,'Feb' RMonth,SUM(FQty) Qty FROM #TmpTable GROUP BY RYear
UNION ALL
SELECT RYear,'Mar' RMonth,SUM(MQty) Qty FROM #TmpTable GROUP BY RYear
UNION ALL
SELECT RYear,'Apr' RMonth,SUM(AQty) Qty FROM #TmpTable GROUP BY RYear

 

4. 转换原理
   用 GROUP 统计之后，之后用 UNION 联合起来即可