select id, max(case type when 0 then data else 0 end) as data, max(case type when 1 then data else 0 end) as data1 from tb gorup by id
select id, max(case type when 0 then data else 0 end) as data, max(case type when 1 then data else 0 end) as data1 from tb group by id
--如果Type不固定,自己拼SQL吧.注意,只有2列转行,可用pivot,其它用case when declare @table table ( id int, data int, [type] int ) insert into @table select 1, 10, 0 union all select 1, 20, 1 union all select 2, 10, 0 union all select 2, 33, 1select * from @table a pivot (max(Data) for [type] in([0], [1])) b /* id 0 1 1 10 20 2 10 33 */
id,
max(case type when 0 then data else 0 end) as data,
max(case type when 1 then data else 0 end) as data1
from
tb
gorup by
id
id,
max(case type when 0 then data else 0 end) as data,
max(case type when 1 then data else 0 end) as data1
from
tb
group by
id
declare @table table
(
id int,
data int,
[type] int
)
insert into @table
select 1, 10, 0 union all
select 1, 20, 1 union all
select 2, 10, 0 union all
select 2, 33, 1select *
from @table a
pivot
(max(Data) for [type] in([0], [1])) b
/*
id 0 1
1 10 20
2 10 33
*/