我有一个表数据:
ID SEQ TYPE VALUE
1 1 T1 1
1 2 T2 1
1 3 T3 4
1 4 T2 1
2 1 T1 4
2 2 T2 5
...结果为
ID T1 T2 T3
1 1 2 4
2 4 5 0
...同一ID把所有TYPE值按列显示,若有相同,则相加。
望高手指教!
ID SEQ TYPE VALUE
1 1 T1 1
1 2 T2 1
1 3 T3 4
1 4 T2 1
2 1 T1 4
2 2 T2 5
...结果为
ID T1 T2 T3
1 1 2 4
2 4 5 0
...同一ID把所有TYPE值按列显示,若有相同,则相加。
望高手指教!
select 1 id, 1 seq, 'T1' type, 1 value from dual union all
select 1 id, 2 seq, 'T2' type, 1 value from dual union all
select 1 id, 3 seq, 'T3' type, 4 value from dual union all
select 1 id, 4 seq, 'T2' type, 1 value from dual union all
select 2 id, 1 seq, 'T1' type, 4 value from dual union all
select 2 id, 2 seq, 'T2' type, 5 value from dual)
SELECT id,
SUM(decode(TYPE, 'T1', VALUE, 0)) t1,
SUM(decode(TYPE, 'T2', VALUE, 0)) T2,
SUM(decode(TYPE, 'T3', VALUE, 0)) T3
FROM tt
GROUP BY id;
with tt as(
select 1 id, 1 seq, 'T1' type, 1 value from dual union all
select 1 id, 2 seq, 'T2' type, 1 value from dual union all
select 1 id, 3 seq, 'T3' type, 4 value from dual union all
select 1 id, 4 seq, 'T2' type, 1 value from dual union all
select 2 id, 1 seq, 'T1' type, 4 value from dual union all
select 2 id, 2 seq, 'T2' type, 5 value from dual)
SELECT id,
SUM(case TYPE WHEN 'T1' then VALUE else 0 END) T1,
SUM(case TYPE WHEN 'T2' then VALUE else 0 END) T2,
SUM(case TYPE WHEN 'T3' then VALUE else 0 END) T3
FROM tt
GROUP BY id
并使用EXECUTE IMMEDIATE 来执行动态SQL。
看下
再发 行转列通用过程
oracle QQ群:54775466
欢迎您的到来
大家一起探讨。
http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.html