表:
id col1 col2
1 A k1
1 B k2
2 C k1
2 A k2
2 D k3
2 A k4
3 B k1
3 A k4
3 D k2
4 C k3
4 C k4想转换成表:
id col1 col2 col3 col4 col5 col6 col7 col8
1 A k1 B k2
2 C k1 A k2 D k3 A k4
3 B k1 A k4
4 C k3 C k4请高人指点,最好用一句sql搞定,不行用过程也行。
id col1 col2
1 A k1
1 B k2
2 C k1
2 A k2
2 D k3
2 A k4
3 B k1
3 A k4
3 D k2
4 C k3
4 C k4想转换成表:
id col1 col2 col3 col4 col5 col6 col7 col8
1 A k1 B k2
2 C k1 A k2 D k3 A k4
3 B k1 A k4
4 C k3 C k4请高人指点,最好用一句sql搞定,不行用过程也行。
解决方案 »
- ORACLE数据库的查询速度问题。
- orcale 10g 的问题,急急急急
- 报表结合
- Oracle Performance: Hotsos 有人用吗?分享分享
- 请教对于NUMBER类型的字段怎样才可以不使用科学计数法存放大于15位的整数?
- oracle论坛cnoug怎么登不上去了?
- 怎么添加一个插入触发器?
- ◆case...when在存储过程中无法使用
- 请教个小问题:oracle的内置函数中,有没有判断某个字段值中是不是存在空格的?
- Couldn't perform the edit because another user changed the record.(再线等待,谢谢解决)
- oracle释放空间
- 求一简单SQL
select 1 id, 'B'col1, 'k2' col2 from dual union all
select 2 id, 'C'col1, 'k1' col2 from dual union all
select 2 id, 'A'col1, 'k2' col2 from dual union all
select 2 id, 'D'col1, 'k3' col2 from dual union all
select 2 id, 'A'col1, 'k4' col2 from dual union all
select 3 id, 'B'col1, 'k1' col2 from dual union all
select 3 id, 'A'col1, 'k4' col2 from dual union all
select 3 id, 'D'col1, 'k2' col2 from dual union all
select 4 id, 'C'col1, 'k3' col2 from dual union all
select 4 id, 'C'col1, 'k4' col2 from dual
)select id,
max(decode(col2,'k1',col1))col1,
max(decode(col2,'k1','k1'))col2,
max(decode(col2,'k2',col1))col3,
max(decode(col2,'k2','k2'))col4,
max(decode(col2,'k3',col1))col5,
max(decode(col2,'k3','k3'))col6,
max(decode(col2,'k4',col1))col7,
max(decode(col2,'k4','k4'))col8
from tt
group by id
order by 1ID COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8
1 A k1 B k2
2 C k1 A k2 D k3 A k4
3 B k1 D k2 A k4
4 C k3 C k4
,max(decode(col2,'k2',col1)) col3,max(decode(col2,'k2','k2')) col4
,max(decode(col2,'k3',col1)) col5,max(decode(col2,'k3','k3')) col6
,max(decode(col2,'k4',col1)) col7,max(decode(col2,'k4','k4')) col8
from tb_test
group by id order by id;
关键是你的col2有几种类型的取值。。如果只有k1--k4这4种,那么这语句没问题select id,max(decode(col2,'k1',col1)) col1,max(decode(col2,'k1','k1')) col2
,max(decode(col2,'k2',col1)) col3,max(decode(col2,'k2','k2')) col4
,max(decode(col2,'k3',col1)) col5,max(decode(col2,'k3','k3')) col6
,max(decode(col2,'k4',col1)) col7,max(decode(col2,'k4','k4')) col8
from tb_test
group by id order by id;