毫无规律的表数据的行列转换方法！！

use test 

go

if object_id('test.dbo.tb') is not null drop table tb

-- 创建数据表

create table tb

(

message int,

time char(4),

orgid int

)

go

--插入测试数据

insert into tb select 123,2010,12

union all select 456,2010,12

union all select 789,2010,14

union all select 963,2010,15

union all select 345,2010,16

union all select 667,2010,16

union all select 423,2010,16

go

--代码实现

select * from tb

/*原数据

message time orgid

------------------------------

123 2010 12

456 2010 12

789 2010 14

963 2010 15

345 2010 16

667 2010 16

423 2010 16

(7 行受影响)

*/

declare @s varchar(max),@i int,@maxnum int,@str varchar(max)

select @i=0,@maxnum=max(num) from (select num=count(*) from tb group by orgid)t

while(@i<@maxnum)

begin

     select @s=isnull(@s+' when orgid='+rtrim(orgid)+' and idd='+rtrim(@i+1)+' then message' , 'case when                orgid='+rtrim(orgid)+' and idd='+rtrim(@i+1)+' then message')  from (select distinct orgid from tb)t

     select @s='message'+rtrim(@i)+'=max('+@s+' end),' +'time'+rtrim(@i)+'=max('+replace(@s,'message','time')+'      end)' ,@str=isnull(@str+','+@s,@s),@s=null,@i=@i+1

end

print @str

exec(';with t as (select idd=row_number()over(partition by orgid order by getdate()),* from tb) select orgid,'+@str+' from t group by orgid')

/*测试结果

orgid message0 time0 message1 time1 message2 time2

-------------------------------------------------------------------------------------------

12 123 2010 456 2010 NULL NULL

14 789 2010 NULL NULL NULL NULL

15 963 2010 NULL NULL NULL NULL

16 345 2010 667 2010 423 2010

(3 行受影响)

*/