SQl 行转列，列转行 (PIVOT，UPIVOT)

1.列转行

表t_pivot

 

转后效果

 

方法 1 (常用方法 case when)

SELECT     buydate,

                  SUM(CASE WHEN type = '生活' THEN typecount ELSE 0 END) AS 生活,

                  SUM(CASE WHEN type = '学习' THEN typecount ELSE 0 END) AS 学习,                   SUM(CASE WHEN type = '动画' THEN typecount ELSE 0 END) AS 动画,

                  SUM(CASE WHEN type = '游戏' THEN typecount ELSE 0 END) AS 游戏 FROM         dbo.t_pivot GROUP BY buydate

方法 2 (PIVOT)

SELECT     buydate,

                  sum(CASE WHEN 生活 IS NULL THEN 0 ELSE 生活 END) AS '生活',

                  sum(CASE WHEN 学习 IS NULL THEN 0 ELSE 学习 END) AS '学习',                   sum(CASE WHEN 动画 IS NULL THEN 0 ELSE 动画 END) AS '动画',

                  sum(CASE WHEN 游戏 IS NULL THEN 0 ELSE 游戏 END) AS '游戏' FROM        t_pivot PIVOT (sum(typecount) FOR type IN (生活, 学习, 动画, 游戏)) AS pvt GROUP BY buydate

 

2.行转列

表t_unpivot

转后效果

 

方法 1 (常用方法 case when)

 

SELECT     stuname, math AS scores, 'math' AS course FROM         dbo.t_unpivot UNION SELECT     stuname, english AS scores, 'english' AS course FROM         dbo.t_unpivot UNION SELECT     stuname, chinese AS scores, 'chinese' AS course FROM         dbo.t_unpivot UNION SELECT     stuname, history AS scores, 'history' AS course FROM         dbo.t_unpivot

方法 2 (UPIVOT)

SELECT     stuname, course, scores FROM        t_unpivot UNPIVOT (scores FOR course IN (math, english, chinese, history)) AS pvt