如:
uu type data
aa 1 209
aa 2 414
aa 10 53
bb 1 93
bb 2 893
bb 10 834
要显示成uu 1 2 10
aa 209 414 53
bb 93 893 834
,type一定是1,2,10这三个数字,可以写死
uu type data
aa 1 209
aa 2 414
aa 10 53
bb 1 93
bb 2 893
bb 10 834
要显示成uu 1 2 10
aa 209 414 53
bb 93 893 834
,type一定是1,2,10这三个数字,可以写死
请完整的把oracle pl/sql语句写给我,谢谢
SQL> with tmp as
2 (
3 select 'aa' uu, 1 type, 209 data from dual
4 union all
5 select 'aa' uu, 2 type, 414 data from dual
6 union all
7 select 'aa' uu, 10 type, 53 data from dual
8 union all
9 select 'bb' uu, 1 type, 93 data from dual
10 union all
11 select 'bb' uu, 2 type, 893 data from dual
12 union all
13 select 'bb' uu, 10 type, 834 data from dual
14 )
15 select uu,
16 max(decode(type, 1, data)) "1",
17 max(decode(type, 2, data)) "2",
18 max(decode(type, 10, data)) "10"
19 from tmp
20 group by uu;
UU 1 2 10
-- ---------- ---------- ----------
aa 209 414 53
bb 93 893 834
15 select uu,
16 max(decode(type, 1, data)) "1",
17 max(decode(type, 2, data)) "2",
18 max(decode(type, 10, data)) "10"
19 from tmp
20 group by uu;
这段,不是上边with那段!with是给出测试环境的!楼主不要理解错!把tmp改成你的表名就可以。
你只要使用以下SQL语句就行了。select uu,
max(decode(type, 1, data)) "1",
max(decode(type, 2, data)) "2",
max(decode(type, 10, data)) "10"
from tbname --tbname换成你的表名就OK了
group by uu;
如果1000行你还采用这种展现方式,那是你自己逻辑的问题.而不是sql语句的问题.
如果你的列不确定,最好用动态sql,自己按3楼的格式去拼sql
MAX(DECODE(TABLE_A.TYPE, 1, DATA)) "1",
MAX(DECODE(TABLE_A.TYPE, 2, DATA)) "2",
MAX(DECODE(TABLE_A.TYPE, 10, DATA)) "10"
FROM (SELECT UU,
TYPE,
DATA,
ROW_NUMBER() OVER(PARTITION BY TYPE ORDER BY UU DESC) SEQ
FROM TABLENAME) TABLE_A
GROUP BY TABLE_A.UU