sql server 行转列及列转行的使用

在我们使用的数据库表中经常需要用到行列互相转换的情况，使用sql 的关键词 UNPIVOT（列转行）和PIVOT（行转列）可轻松实现行列转换。

一、列转行：员工月份排班表存储是采用1号~31号作为列的方式进行存储的

 

 

现通过 UNPIVOT 将每天的班次用行进行展示，sql 如下：

SELECT distinct  t.Pb_Job_No,
t.Year_Month ,convert(int, REPLACE( t.day,'day','')) as  day,classno FROM Scheduling_Info
UNPIVOT(classno FOR day IN(Day1,Day2,Day3,Day4,Day5,Day6,Day7 ,Day8 ,Day9,Day10,
        Day11,Day12,Day13,Day14,Day15,Day16,Day17,Day18,Day19,Day20,
        Day21,Day22,Day23,Day24,Day25,Day26,Day27,Day28,Day29,Day30,Day31)) T      
where Year_Month='2020-05' and Pb_Job_No='0997' order by Pb_Job_No,day

这里的关键词是

UNPIVOT(classno FOR day IN('日期列名') ,其中 ‘day’是存储日期的列，classno 是存储原有班次的列

查询结果如下：

 

二、行转列：如果将上述列转行查询的结果表定义为 Scheduling_DayInfo，进行逆转为原始表，那么sql 语句为：

SELECT Pb_Job_No,[1],[2] ,[3] ,[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],
[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]
from 
Scheduling_DayInfo
　PIVOT (　　max(classno)　FOR [day]  IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],
[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])　　) 
t

结果为：

 

 此处的 in 必须是列 day 中的值，使用pivot 需要用到聚合函数 （sum，count，avg，max，min 等），使用的场景如考试成绩 sum(score) ,年度销售业绩等，由于此处不需要统计但是又必须用聚合函数，所以使用max 凑合，因为这些函数可以接受字符类型的参数。

平时虽有用到这两个函数 ，但是没有总结，长时间不用就忘记如何使用了，所以今天记录下来，加深印象并方便日后查看。