SQL行转列PIVOT关键字的用法

来源：互联网发布：seo原创怎么写编辑：程序博客网时间：2024/06/06 00:10

昨天写报表遇到行转列，原来都是使用动态sql来实现，这次尝试使用了下pivot来实现，pivot是sql server2005后加入的关键字，它使用起来比较方便，比起动态sql会简化很多。

基本语法：select 列1,[A],[B],[C]... from table pivot (聚合函数(列1) for 列2 in ([A],[B],[C]......)) as t

例1：

1.数据准备

CREATE TABLE #profit(Yer_mon VARCHAR(20), dep_nam VARCHAR(20), prf_amt INT)INSERT INTO #profit( Yer_mon, dep_nam, prf_amt )VALUES ( '201301', '深圳分行', 10 )INSERT INTO #profit( Yer_mon, dep_nam, prf_amt )VALUES ( '201301', '北京分行', 20 )INSERT INTO #profit( Yer_mon, dep_nam, prf_amt )VALUES ( '201302', '上海分行', 5 )INSERT INTO #profit( Yer_mon, dep_nam, prf_amt )VALUES ( '201303', '深圳分行', 20 )INSERT INTO #profit( Yer_mon, dep_nam, prf_amt )VALUES ( '201303', '广州分行', 50 )SELECT * FROM #profit

Yer_mon dep_nam prf_amt
-------------------- -------------------- -----------
201301 深圳分行 10
201301 北京分行 20
201302 上海分行 5
201303 深圳分行 20
201303 广州分行 50

下面我们将Yer_mon列中的内容转为列名，sql：

SELECT  dep_nam ,[201301] ,[201302] ,[201303]FROM    #profit PIVOT ( SUM(prf_amt) FOR Yer_mon IN ( [201301],[201302],[201303] ) ) AS t

输出：

dep_nam 201301 201302 201303
-------------------- ----------- ----------- -----------
北京分行 20 NULL NULL
广州分行 NULL NULL 50
上海分行 NULL 5 NULL
深圳分行 10 NULL 20

注意：1.pivot行转列中展示列必须使用聚合函数，如：sum、avg、count、max、min等；

2.pivot处理的列其实就相当于一个数据表，这里必须给它加上别名；

问：如果我们要转的列内容是日期类型或者字符串类型怎么办？这个时候可以使用max或者min函数巧妙的达到我们的目的，请看下面这个例子：

例2：

我们这里有张表#t：

SaleServiceGUID ServiceProc CompleteDate
------------------------------------ ------------------------------ -----------------------
002DBBDD-7C90-4F62-8071-00749D89BA03 到访日期 2012-12-25
002DBBDD-7C90-4F62-8071-00749D89BA03 未办理 2012-06-06
002DBBDD-7C90-4F62-8071-00749D89BA03 入伙日期 2012-12-25
002DBBDD-7C90-4F62-8071-00749D89BA03 已办理产权 2013-04-29
002DBBDD-7C90-4F62-8071-00749D89BA03 物业费起计时间 2013-02-01
A31087ED-27B1-422A-BEB2-057B87FA6782 物业费起计时间 2013-10-01
A31087ED-27B1-422A-BEB2-057B87FA6782 入伙日期 2013-07-03
A31087ED-27B1-422A-BEB2-057B87FA6782 到访日期 2013-07-03
A31087ED-27B1-422A-BEB2-057B87FA6782 未办理 2012-12-29

我们要把ServiceProc转为列名，数据内容是字段CompleteDate，在已知列数情况下我们可以这样写：

SELECT SaleServiceGUID,[未办理],[到访日期],[入伙日期],[物业费起计时间],[已办理产权] FROM #t PIVOT ( MAX(CompleteDate) FOR ServiceProc IN ([未办理],[到访日期],[入伙日期],[物业费起计时间],[已办理产权])) AS t

输出：

SaleServiceGUID 未办理到访日期入伙日期物业费起计时间已办理产权
------------------------------------ ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
002DBBDD-7C90-4F62-8071-00749D89BA03 2012-06-06 2012-12-25 2012-12-25 2013-02-01 2013-04-29
A31087ED-27B1-422A-BEB2-057B87FA6782 2012-12-29 2013-07-03 2013-07-03 2013-10-01 NULL

注意：这里我们使用了max聚合函数来取日期字段，同理如果是字符串类型的数据也可以使用这种写法。

0 0