sqlserver查询多行并一行
Id 班级 老师 任职日期
11张2012-01-02
21李2012-04-02
31王2012-05-02
42刘2012-02-02
52赵2012-05-02
------------------------
如上结果集,我想查出的结果为这样:
班级 老师 任职日期 老师 任职日期 老师 任职日期
1 张 2012-01-02 李 2012-04-02 王 2012-05-02
2 刘 2012-02-02 赵 2012-05-02 NULL NULL
------------------------
其中每种班级的行数不确定,以最多行数的班级为准,不够的为NULL
比如班级1有3行,班级2有2行,则以班级1为准。(其中每种班级的行数不确定)
相当于纵向变横向排列了,而且按日期先后。请问怎么做。
[解决办法]
if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([Id] int,[班级] int,[老师] varchar(2),[任职日期] datetime)insert [tb]select 1,1,'张','2012-01-02' union allselect 2,1,'李','2012-04-02' union allselect 3,1,'王','2012-05-02' union allselect 4,2,'刘','2012-02-02' union allselect 5,2,'赵','2012-05-02'godeclare @sql varchar(8000)select @sql=isnull(@sql+',','') +'max(case when rn='+ltrim(rn)+' then 老师 end) as [老师'+ltrim(rn)+'],' +'max(case when rn='+ltrim(rn)+' then convert(varchar(10),任职日期,120) end) as [任职日期'+ltrim(rn)+']'from( select distinct rn=(select count(1) from tb where 班级=t.班级 and id<=t.id) from tb t) t2exec ('select 班级,'+@sql+' from (' +'select *,rn=(select count(1) from tb where 班级=t.班级 and id<=t.id) from tb t) t2' +' group by 班级' )/**班级 老师1 任职日期1 老师2 任职日期2 老师3 任职日期3----------- ---- ---------- ---- ---------- ---- ----------1 张 2012-01-02 李 2012-04-02 王 2012-05-022 刘 2012-02-02 赵 2012-05-02 NULL NULL(2 行受影响)**/
[解决办法]
--> 测试数据:[test]if object_id('[test]') is not null drop table [test]create table [test]([Id] int,[班级] int,[老师] varchar(2),[任职日期] datetime)goinsert [test]select 1,1,'张','2012-01-02' union allselect 2,1,'李','2012-04-02' union allselect 3,1,'王','2012-05-02' union allselect 4,2,'刘','2012-02-02' union allselect 5,2,'赵','2012-05-02'godeclare @str varchar(2000)set @str=''select @str=@str+',[教师'+LTRIM(px)+']=max(case when px='+LTRIM(px) +' then [老师] else null end),[任职日期' +LTRIM(px)+']=max(case when px='+LTRIM(px)+' then convert(varchar(10),任职日期,120) else null end)'from ( select px=ROW_NUMBER()over(partition by [班级] order by id), * from test )tgroup by pxexec('select [班级]'+@str+' from(select px=ROW_NUMBER()over(partition by [班级] order by id), * from test)t group by [班级]')/*班级 教师1 任职日期1 教师2 任职日期2 教师3 任职日期31 张 2012-01-02 李 2012-04-02 王 2012-05-022 刘 2012-02-02 赵 2012-05-02 NULL NULL*/