求SQL 行列转换
表 A
tid id value
a 1 10
a 2 20
b 3 10
c 4 30
c 5 40
d 6 10
e 7 0
f 8 10
转换成
tid value1 value2
a 10 20
b 10 0
c 30 40
d 10 0
e 0 10
value1的值取同tid中id小的value值 id的值是自增的 SQL要满足更多记录的情况
非常感谢大家！！！！
[解决办法]
with tb(tid,id,value)
as(
select 'a',1,10 union all
select 'a',2,20 union all
select 'b',3,10 union all
select 'c',4,30 union all
select 'c',5,40 union all
select 'd',6,10 union all
select 'e',7,0 union all
select 'e',8,10
)
select tid,min(value) value1,(case when (select count(1) from tb tb2 where tb2.tid=tb1.tid)>1 then max(value) else 0 end) value2 from tb tb1 group by tid
[解决办法]
膜拜下2楼，在2楼的基础上稍微改一下就行
with tb(tid, id, value)as (
select 'a', 1, 10 union all
select 'a', 2, 20 union all
select 'b', 3, 10 union all
select 'c', 4, 30 union all
select 'c', 5, 40 union all
select 'd', 6, 10 union all
select 'e', 7, 0 union all
select 'f', 8, 10)
select tid,max(value) value2,
(case when (select count(1) from tb tb2 where tb2.tid=tb1.tid)>1 then min(value) else 0 end)
value1 from tb tb1 group by tid
else 0 end) value2 from tb tb1 group by tid
[解决办法]
select a.tid,b.value value1,isnull(e.value2,0) value2 from
(select tid,min(id) id from tb) a join tb b on a.tid=b.tid and a.id=b.id
left join
(select c.tid,d.value2 from (select tid,max(id) id from tb group by tid having count(*)>1) c join tb d on c.tid=d.tid and c.id=d.id) e on b.tid=e.tid
