SQL2008 R2 SP3中使用pivot实现行转列

同事遇到一个问题，怎么实现行转列，参考了别人的示例，我是这样解决的。

创建数据源：

create table #temp (
    objectid int,
    sitename nvarchar(32),
    cities nvarchar(32),
    projectdurationno nvarchar(32),
    fullname nvarchar(32),
    createdate datetime,
    nodename nvarchar(32)
)

insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(2,'1','海口市','','','10 30 2015 10:26AM','开始')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(2,'1','海口市','','','10 30 2015 10:26AM','规划库')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(2,'1','海口市','','','10 30 2015 10:27AM','投资')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(2,'1','海口市','','','10 30 2015  4:37PM','站点')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(2,'1','海口市','','','10 30 2015  4:37PM','方案')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(2,'1','海口市','','','10 30 2015  4:38PM','集成')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(4,'1','海口市','','','10 30 2015  6:03PM','开始')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(4,'1','海口市','','','10 30 2015  6:03PM','规划库')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(4,'1','海口市','','','10 30 2015  6:03PM','投资')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(4,'1','海口市','','','10 30 2015  9:31PM','站点')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(4,'1','海口市','','','10 30 2015  9:31PM','方案')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(4,'1','海口市','','','10 30 2015  9:32PM','集成')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(4,'1','海口市','','','10 30 2015  9:32PM','光缆厂家')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(6,'1','海口市','','','11  3 2015  3:13PM','开始')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(6,'1','海口市','','','11  3 2015  3:13PM','规划库')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(6,'1','海口市','','','11  3 2015  3:13PM','投资')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(6,'1','海口市','','','11  3 2015  3:14PM','站点')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(6,'1','海口市','','','11  3 2015  3:14PM','方案')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(6,'1','海口市','','','11  3 2015  3:18PM','集成')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(6,'1','海口市','','','11  3 2015  3:18PM','光缆厂家')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(6,'1','海口市','','','11  3 2015  3:19PM','分布系统、设备安装')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(6,'1','海口市','','','11  3 2015  3:19PM','光缆到位')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(6,'1','海口市','','','11  3 2015  3:24PM','PTN上线')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(6,'1','海口市','','','11  3 2015  3:25PM','开通')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(6,'1','海口市','','','11  3 2015  3:26PM','结束')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(8,'1','海口市','','','11  3 2015  3:45PM','开始')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(8,'1','海口市','','','11  3 2015  3:45PM','规划库')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(8,'1','海口市','','','11  3 2015  3:45PM','投资')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(8,'1','海口市','','','11  3 2015  3:46PM','站点')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(8,'1','海口市','','','11  3 2015  3:46PM','方案')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(9,'1','海口市','','','11  3 2015  4:26PM','开始')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(9,'1','海口市','','','11  3 2015  4:26PM','规划库')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(9,'1','海口市','','','11  3 2015  4:26PM','投资')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(10,'1','海口市','','','11  3 2015  4:29PM','开始')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(10,'1','海口市','','','11  3 2015  4:29PM','规划库')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(10,'1','海口市','','','11  3 2015  4:29PM','投资')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(11,'1','海口市','','','11  3 2015  4:33PM','开始')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(11,'1','海口市','','','11  3 2015  4:33PM','规划库')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(11,'1','海口市','','','11  3 2015  4:33PM','投资')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(11,'1','海口市','','','11  3 2015  4:33PM','站点')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(11,'1','海口市','','','11  3 2015  4:41PM','方案')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(11,'1','海口市','','','11  3 2015  4:42PM','集成')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(11,'1','海口市','','','11  3 2015  4:42PM','光缆厂家')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(11,'1','海口市','','','11  3 2015  4:42PM','分布系统、设备安装')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(11,'1','海口市','','','11  3 2015  4:43PM','光缆到位')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(11,'1','海口市','','','11  3 2015  4:44PM','PTN上线')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(11,'1','海口市','','','11  3 2015  4:44PM','开通')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(11,'1','海口市','','','11  3 2015  4:46PM','结束')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(7,'2','海口市','','','11  3 2015  3:30PM','开始')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(7,'2','海口市','','','11  3 2015  3:30PM','规划库')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(7,'2','海口市','','','11  3 2015  3:30PM','投资')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(7,'2','海口市','','','11  3 2015  3:31PM','站点')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(7,'2','海口市','','','11  3 2015  3:31PM','方案')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(7,'2','海口市','','','11  3 2015  3:32PM','集成')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(7,'2','海口市','','','11  3 2015  3:32PM','光缆厂家')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(5,'2','海口市','','','11  3 2015 10:31AM','开始')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(5,'2','海口市','','','11  3 2015 10:31AM','规划库')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(5,'2','海口市','','','11  3 2015 10:34AM','投资')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(3,'7','海口市','','','10 30 2015  5:36PM','开始')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(3,'7','海口市','','','10 30 2015  5:36PM','规划库')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(3,'7','海口市','','','10 30 2015  5:36PM','投资')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(3,'7','海口市','','','10 30 2015  5:37PM','站点')
insert into #temp(objectid,sitename,cities,projectdurationno,fullname,createdate,node_name)values(3,'7','海口市','','','10 30 2015  5:37PM','方案')

 

方案一、使用pivot进行行转列：

select objectid,SiteName,[开始],[规划库],[投资],[站点],[方案],[集成]
    from(
        select objectid,sitename,NODE_NAME,createdate from #temp
    ) as t10 
    pivot(
        max(createdate) 
        for node_name in 
        ([开始],[规划库],[投资],[站点],[方案],[集成])
    ) as t11
    order by sitename,objectid

 方案二、group by 方案

    
select t10.sitename,t10.objectid,
(case t10.node_name when '开始' then t10.createdate else null end) as '开始',
(case t10.node_name when '规划库' then t10.createdate else null end) as '规划库',
(case t10.node_name when '投资' then t10.createdate else null end) as '投资',
(case t10.node_name when '站点' then t10.createdate else null end) as '站点',
(case t10.node_name when '方案' then t10.createdate else null end) as '方案',
(case t10.node_name when '集成' then t10.createdate else null end) as '集成'
into #temp10
from #temp as t10

select * from #temp10

select objectid,sitename,
max([开始]) as [开始],
max([规划库]) as [规划库],
max([投资]) as [投资],
max([站点]) as [站点],
max([方案]) as [方案],
max([集成]) as [集成]
into #temp12
from #temp10
group by objectid,sitename

对比之后两种方案结果一致。

执行效果：

2    1    2015-10-30 10:26:59.663    2015-10-30 10:26:59.683    2015-10-30 10:27:32.013    2015-10-30 16:37:14.153    2015-10-30 16:37:31.387    2015-10-30 16:38:51.797
4    1    2015-10-30 18:03:09.720    2015-10-30 18:03:09.737    2015-10-30 18:03:32.650    2015-10-30 21:31:11.683    2015-10-30 21:31:47.820    2015-10-30 21:32:28.450
6    1    2015-11-03 15:13:16.393    2015-11-03 15:13:16.410    2015-11-03 15:13:40.603    2015-11-03 15:14:30.073    2015-11-03 15:14:50.243    2015-11-03 15:18:15.117
8    1    2015-11-03 15:45:21.577    2015-11-03 15:45:21.593    2015-11-03 15:45:39.000    2015-11-03 15:46:08.953    2015-11-03 15:46:22.547    NULL
9    1    2015-11-03 16:26:01.500    2015-11-03 16:26:01.520    2015-11-03 16:26:22.587    NULL    NULL    NULL
10    1    2015-11-03 16:29:10.687    2015-11-03 16:29:10.697    2015-11-03 16:29:28.677    NULL    NULL    NULL
11    1    2015-11-03 16:33:02.083    2015-11-03 16:33:02.107    2015-11-03 16:33:23.270    2015-11-03 16:33:59.510    2015-11-03 16:41:48.663    2015-11-03 16:42:14.450
5    2    2015-11-03 10:31:13.560    2015-11-03 10:31:13.580    2015-11-03 10:34:41.880    NULL    NULL    NULL
7    2    2015-11-03 15:30:07.367    2015-11-03 15:30:07.377    2015-11-03 15:30:31.943    2015-11-03 15:31:29.560    2015-11-03 15:31:45.950    2015-11-03 15:32:08.257
3    7    2015-10-30 17:36:29.193    2015-10-30 17:36:29.237    2015-10-30 17:36:54.887    2015-10-30 17:37:24.507    2015-10-30 17:37:47.057    NULL