SQL ServerSQLServer处理行转列和列转行

驾驭SQL Server
行转列和列转行

1、列转行

     
数据通过估测计算加工后会直接生成前端图表需求的数据源,不过程序里又供给把该数额通过列转行写入中间表中,下次再查询该数量时直接从中间表查询数据。

1.1 列换行语法

SQL Server 1😉

table_source

UNPIVOT(

value_column

FOR pivot_column

IN(<column_list>)

)

SQL Server 2😉

 

1.2  行转列案例

SQL Server 3😉

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

SQL Server 4😉

 

SQL Server 5

2、 行转列

       行转列首即便从中间表里查询数据,SQL
SE汉兰达VE哈弗二零零六以下的本子则足以使用聚合函数来形成。

2.1 行转列语法

SQL Server 6😉

table_source

PIVOT(

聚合函数(value_column)

FOR pivot_column

IN(<column_list>)

)

SQL Server 7😉

2.2、使用PIVOT实现

SQL Server 8😉

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

SQL Server 9😉

2.③ 、使用聚合函数完毕

SQL Server 10😉

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

SQL Server 11😉

 

参考资料

http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html

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

相关文章