| 注册
请输入搜索内容

热门搜索

Java Linux MySQL PHP JavaScript Hibernate jQuery Nginx
jopen
9年前发布

SQL Server 行转列和列转行

原文  http://www.cnblogs.com/sword-successful/p/4814840.html
 

说在前面

做一个数据统计和分析的项目,每天面对着各种数据,经过存储过程从源表计算汇总后需要写入中间结果表以提高数据使用效率,那么此时就需要用到行转列和列转行。

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

http://www.cnblogs.com/aspnethot/articles/1762665.html

 本文由用户 jopen 自行上传分享,仅供网友学习交流。所有权归原作者,若您的权利被侵害,请联系管理员。
 转载本站原创文章,请注明出处,并保留原始链接、图片水印。
 本站是一个以用户分享为主的开源技术平台,欢迎各类分享!
 本文地址:https://www.open-open.com/lib/view/open1442457492430.html
SQL Server 数据库服务器