4,819
社区成员




create table #tb(id int,name varchar(10),value int,class varchar(10))
insert into #tb
select 1,'我',11,'水'
union all select 2,'我',12,'气'
union all select 3,'你',21,'水'
union all select 4,'他',31,'水'
union all select 5,'他',32,'气'
union all select 6,'她',41,'水'
select *
from
(
select *,id=row_number() over(order by [水value])
from (
select name,[水value]=max(case when class='水' then value end),[气value]=max(case when class='气' then value end)
from #tb
group by name
)t
)a
order by id
/*
id name 水value 气value
---------------------------------
1 我 11 12
2 你 21 NULL
3 他 31 32
4 她 41 NULL
*/