我有个表结构是这样。
id type name sex age post address
1 1 a 1 20 dd sss
1 2 b 2 21 aa qqq
1 3 c 1 22 bb www
1 4 d 2 23 cc eee现在想转换成为这样
以type为拆分条件
id name1 sex1 age1 post1 address1 name2 sex2 age2 post2 address2 name3 sex3 age3 post3 address3 .....
1 a 1 20 dd sss b 2 21 aa qqq c 1 22 bb www这样我该如何写这个sql语句呢我的数据库是SqlServer2005
id type name sex age post address
1 1 a 1 20 dd sss
1 2 b 2 21 aa qqq
1 3 c 1 22 bb www
1 4 d 2 23 cc eee现在想转换成为这样
以type为拆分条件
id name1 sex1 age1 post1 address1 name2 sex2 age2 post2 address2 name3 sex3 age3 post3 address3 .....
1 a 1 20 dd sss b 2 21 aa qqq c 1 22 bb www这样我该如何写这个sql语句呢我的数据库是SqlServer2005
max(case type then 1 then name end) name1,
max(case type then 1 then sex end) sex1,
max(case type then 1 then age end) age1,
max(case type then 1 then post end) post1,
max(case type then 1 then address end) address1,
max(case type then 2 then name end) name2,
max(case type then 2 then sex end) sex2,
max(case type then 2 then age end) age2,
max(case type then 2 then post end) post2,
max(case type then 2 then address end) address2,
max(case type then 3 then name end) name3,
max(case type then 3 then sex end) sex3,
max(case type then 3 then age end) age3,
max(case type then 3 then post end) post3,
max(case type then 3 then address end) address3,
max(case type then 4 then name end) name4,
max(case type then 4 then sex end) sex4,
max(case type then 4 then age end) age4,
max(case type then 4 then post end) post4,
max(case type then 4 then address end) address4
from tb
group by id
declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case type when ''' + cast(type as varchar) + ''' then name end) [name' + cast(type as varchar) + ']'
+ ' , max(case type when ''' + cast(type as varchar) + ''' then sex end) [sex' + cast(type as varchar) + ']'
+ ' , max(case type when ''' + cast(type as varchar) + ''' then age end) [age' + cast(type as varchar) + ']'
+ ' , max(case type when ''' + cast(type as varchar) + ''' then post end) [post' + cast(type as varchar) + ']'
+ ' , max(case type when ''' + cast(type as varchar) + ''' then address end) [address' + cast(type as varchar) + ']'
from (select distinct type from tb) as a
set @sql = @sql + ' from tb group by id'
exec(@sql)