求,行转换列的问题
id sub pp
1 d c:\s
1 s_id 54
2 d d:\f
2 s_id 66
转换为
id s_id d
1 54 c:\s
2 66 d:\f
[解决办法]
declare @a table(id int, sub varchar(10), pp varchar(10))
insert @a select 1 ,'d', 'c:\s'
union all select 1 ,'s_id' ,'54'
union all select 2 ,'d' ,'d:\f'
union all select 2 ,'s_id' ,'66'
select id,
min(case when sub='s_id' then pp end) s_id,
min(case when sub='d' then pp end) d
from @a group by id
--result
/*
id s_id d
----------- ---------- ----------
1 54 c:\s
2 66 d:\f
（所影响的行数为 2 行）
*/
[解决办法]
declare @t table(id int, sub varchar(10), pp varchar(10)) insert @t select 1 , 'd ', 'c:\s ' union all select 1 , 's_id ' , '54 ' union all select 2 , 'd ' , 'd:\f ' union all select 2 , 's_id ' , '66 ' select id, (select max(pp) from @t where id=t.id and isnumeric(pp)=1), (select max(pp) from @t where id=t.id and isnumeric(pp)=0) from @t tgroup by ID--------（所影响的行数为 4 行）id ----------- ---------- ---------- 1 54 c:\s 2 66 d:\f （所影响的行数为 2 行）
