横列相互转换

列转行

WITH T
AS
(
    SELECT 1 as TeamId, '测试团队1' as Team, 80 'MEN', 20 'WOMEN', 99 'add'
    UNION
    SELECT 2 as TeamId, '测试团队2' as Team, 30 'MEN', 70 'WOMEN', 99 'add'
)
------------列传行----------------
SELECT TeamId,Team,TYPE=ATTRIBUTE,CNT=VALUE
FROM T
UNPIVOT (
    VALUE FOR ATTRIBUTE IN ([MEN],[WOMEN],[add])
) AS UPV

查询结果如下截图所示：

行转列 

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 RIVOT (SUM(CENT) FOR ITEM IN ([MEN],[WOMEN])) A
--error
--消息 321，级别 15，状态 1，第 12 行
--SUM 不是可识别的表提示选项。如果它要作为表值函数或 CHANGETABLE 函数的参数，请确保您的数据库兼容模式设置为 90。

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

查询结果如下截图所示：

 

参考自：快乐地编程