sqlserver多行转一行
来源:互联网 发布:看股票行情用什么软件 编辑:程序博客网 时间:2024/05/18 01:15
http://topic.csdn.net/u/20120705/11/e3c6d860-a46c-475c-808e-859475e9ad45.html?r=79037815--> 测试数据:[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*/