有如下表:
rq xm
2011-01-01 00:00:01 A
2011-01-01 00:00:02 A
(不确定行数)
……
2011-01-01 00:00:01 b
2011-01-01 00:00:02 b
不确定行数……得出如下:
xm 1 2 ……
A 2011-01-01 00:00:01 2011-01-01 00:00:02 ……
b 2011-01-01 00:00:01 2011-01-01 00:00:02 ……
别见笑啊,麻烦能帮助一下~
rq xm
2011-01-01 00:00:01 A
2011-01-01 00:00:02 A
(不确定行数)
……
2011-01-01 00:00:01 b
2011-01-01 00:00:02 b
不确定行数……得出如下:
xm 1 2 ……
A 2011-01-01 00:00:01 2011-01-01 00:00:02 ……
b 2011-01-01 00:00:01 2011-01-01 00:00:02 ……
别见笑啊,麻烦能帮助一下~
insert into tb
select 'A','2011-01-01 00:00:01' union all
select 'A','2011-01-01 00:00:02' union all
select 'B','2011-01-01 00:00:01' union all
select 'B','2011-01-01 00:00:02' union all
select 'B','2011-01-01 00:00:03'select no=ROW_NUMBER() over(partition by xm order by rq),* into ##tb from tbdeclare @sql varchar(8000)
set @sql = 'select xm '
select @sql = @sql + ' , max(case no when ' + rtrim(no) + ' then rq end) [' + RTRIM(no) + ']'
from ##tb group by no
set @sql = @sql + ' from ##tb group by xm'
exec(@sql) drop table ##tb/*
(5 行受影响)
xm 1 2 3
---- ----------------------- ----------------------- -----------------------
A 2011-01-01 00:00:01.000 2011-01-01 00:00:02.000 NULL
B 2011-01-01 00:00:01.000 2011-01-01 00:00:02.000 2011-01-01 00:00:03.000
警告: 聚合或其他 SET 操作消除了 Null 值。(2 行受影响)
set @sql = 'select xm '
select @sql = @sql + ' , max(case no when ' + rtrim(id) + ' then rq end) [' + RTRIM(id) + ']'
from (select id=row_number()over(partition by xm order by rq),* from tb)t group by id set @sql = @sql + ' from (select id=row_number()over(partition by xm order by rq),* from tb)t group by xm'
exec(@sql)
set @sql = 'select xm '
select @sql = @sql + ' , max(case id when ' + rtrim(id) + ' then rq end) [' + RTRIM(id) + ']'
from (select id=row_number()over(partition by xm order by rq),* from tb)t group by id set @sql = @sql + ' from (select id=row_number()over(partition by xm order by rq),* from tb)t group by xm'
exec(@sql)