SQL Server 行转列和列转行
jopen
9年前
说在前面
做一个数据统计和分析的项目，每天面对着各种数据，经过存储过程从源表计算汇总后需要写入中间结果表以提高数据使用效率，那么此时就需要用到行转列和列转行。
1、列转行
数据经过计算加工后会直接生成前端图表需要的数据源，但是程序里又需要把该数据经过列转行写入中间表中，下次再查询该数据时直接从中间表查询数据。
1.1 列换行语法
table_source UNPIVOT( value_column FOR pivot_column IN(<column_list>) )
1.2 行转列案例
WITH T AS ( SELECT 1 as TeamId,'测试团队1' as Team,80 'MEN',20 'WOMEN' UNION SELECT 2 as TeamId,'测试团队2' as Team,30 'MEN',70 'WOMEN' ) ---列转行------------------------------------ SELECT TeamId,Team ,TYPE=ATTRIBUTE,CNT=VALUE FROM T UNPIVOT ( VALUE FOR ATTRIBUTE IN ([MEN],[WOMEN]) ) AS UPV
2、 行转列
行转列主要是从中间表里查询数据，SQL SERVER2005以下的版本则可以使用聚合函数来完成。
2.1 行转列语法
table_source PIVOT( 聚合函数（value_column） FOR pivot_column IN(<column_list>) )
2.2、使用PIVOT实现
WITH T AS ( SELECT 1 AS ID,'测试团队1' TEAM,'MEN' ITEM,80 CENT UNION SELECT 1 AS ID,'测试团队1' TEAM,'WOMEN' ITEM,20 CENT UNION SELECT 2 AS ID,'测试团队2' TEAM,'MEN' ITEM,30 CENT UNION SELECT 2 AS ID,'测试团队2' TEAM,'WOMEN' ITEM,70 CENT ) SELECT * FROM T PIVOT (SUM(CENT) FOR ITEM IN ([MEN],[WOMEN])) A
2.3、使用聚合函数实现
WITH T AS ( SELECT 1 AS ID,'测试团队1' TEAM,'MEN' ITEM,80 CENT UNION SELECT 1 AS ID,'测试团队1' TEAM,'WOMEN' ITEM,20 CENT UNION SELECT 2 AS ID,'测试团队2' TEAM,'MEN' ITEM,30 CENT UNION SELECT 2 AS ID,'测试团队2' TEAM,'WOMEN' ITEM,70 CENT ) SELECT ID,TEAM, SUM(CASE WHEN ITEM='MEN' THEN CENT ELSE 0 END) 'MEN', SUM(CASE WHEN ITEM='WOMEN' THEN CENT ELSE 0 END) 'WOMEN' FROM T GROUP BY ID,TEAM
参考资料
http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html