现在有类似这样的数据:
ID TYPE QTY
01 abc 3
01 ab 2
01 a 1
02 defg 7
02 def 6
02 de 5
02 d 4我希望能以这样的形式输出:
ID TYPE QTY TYPE QTY TYPE QTY TYPE QTY
01 abc 3 ab 2 a 1
02 defg 7 def 6 de 5 d 4哪位高手能帮忙解决呀?
是Oracle的数据库,用的Orange,万分感谢~~
ID TYPE QTY
01 abc 3
01 ab 2
01 a 1
02 defg 7
02 def 6
02 de 5
02 d 4我希望能以这样的形式输出:
ID TYPE QTY TYPE QTY TYPE QTY TYPE QTY
01 abc 3 ab 2 a 1
02 defg 7 def 6 de 5 d 4哪位高手能帮忙解决呀?
是Oracle的数据库,用的Orange,万分感谢~~
解决方案 »
- full join 多表连接,出现重复数据,求解决方法
- 问几个oracle问题
- *******如何解锁*******
- 弱弱的问下数据库中什么是基于成本的优化(急!)
- 导入数据
- 现有一个字段数据为1到N不等,是主键数据,还有个空的字符字段 我想修改空字符字段里的数据要求格式如下V0000001后面的那个1就是主键修改数据必须保持在8位
- 在oracle中如何调用外部程序?---------急
- 菜鸟问题,oracle怎么登陆不了?
- 我的问题高手们帮个忙吧,再加100分给有杰出贡献的高手,今晚就结贴如果满意的话
- 冷备份后,在另外一台机器上启动,提示:SGA not initialized
- sqlplus和pl/sql developer不同步
- (Help)如何把Oracle数据库E-R图导出到Visco?(在线等)
select id,max(decode(rn,1,type,null)),max(decode(rn,1,QTY,null)),max(decode(rn,2,type,null)),max(decode(rn,2,QTY,null)),
max(decode(rn,3,type,null)),max(decode(rn,13,QTY,null)),max(decode(rn,4,type,null)),max(decode(rn,4,QTY,null))
from t
group by id
SQL> with t1 as (select id,type,qty,row_number()over(partition by id order by type desc) rn from tb_row_col )
2 select id,max(decode(rn,1,type,null)),max(decode(rn,1,QTY,null)),max(decode(rn,2,type,null)),max(decode(rn,2,QTY,null)),
3 max(decode(rn,3,type,null)),max(decode(rn,3,QTY,null)),max(decode(rn,4,type,null)),max(decode(rn,4,QTY,null))
4 from t1
5 group by id;
ID MAX(DECODE(RN,1,TYPE,NULL)) MAX(DECODE(RN,1,QTY,NULL)) MAX(DECODE(RN,2,TYPE,NULL)) MAX(DECODE(RN,2,QTY,NULL)) MAX(DECODE(RN,3,TYPE,NULL)) MAX(DECODE(RN,3,QTY,NULL)) MAX(DECODE(RN,4,TYPE,NULL)) MAX(DECODE(RN,4,QTY,NULL))
---------- --------------------------- -------------------------- --------------------------- -------------------------- --------------------------- -------------------------- --------------------------- --------------------------
01 abc 3 ab 2 a 1
02 defg 7 def 6 de 5 d 4
SQL>
如果我其他值转成0,那么就不能用min了,用min你会得到一堆0.
我复制粘贴就是这个效果
SQL> with t1 as (select id,type,qty,row_number()over(partition by id order by type desc) rn from tb_row_col )
2 2 select id,min(decode(rn,1,type,0)),min(decode(rn,1,QTY,0)),min(decode(rn,2,type,0)),min(decode(rn,2,QTY,0)),
3 3 min(decode(rn,3,type,0)),min(decode(rn,3,QTY,0)),min(decode(rn,4,type,0)),min(decode(rn,4,QTY,0))
4 4 from t1
5 5 group by id
6 /
SQL>
SQL> /
with t1 as (select id,type,qty,row_number()over(partition by id order by type desc) rn from tb_row_col )
2 select id,min(decode(rn,1,type,0)),min(decode(rn,1,QTY,0)),min(decode(rn,2,type,0)),min(decode(rn,2,QTY,0)),
3 min(decode(rn,3,type,0)),min(decode(rn,3,QTY,0)),min(decode(rn,4,type,0)),min(decode(rn,4,QTY,0))
4 from t1
5 group by id
ORA-00928: missing SELECT keyword
SQL>
我是在XP系统下用的plsql
2 2 select id,min(decode(rn,1,type,0)),min(decode(rn,1,QTY,0)),min(decode(rn,2,type,0)),min(decode(rn,2,QTY,0)),
3 3 min(decode(rn,3,type,0)),min(decode(rn,3,QTY,0)),min(decode(rn,4,type,0)),min(decode(rn,4,QTY,0))
4 4 from t1
5 5 group by id
6 /SQL> /
with t1 as (select id,type,qty,row_number()over(partition by id order by type desc) rn from tb_row_col )
2 select id,min(decode(rn,1,type,0)),min(decode(rn,1,QTY,0)),min(decode(rn,2,type,0)),min(decode(rn,2,QTY,0)),
3 min(decode(rn,3,type,0)),min(decode(rn,3,QTY,0)),min(decode(rn,4,type,0)),min(decode(rn,4,QTY,0))
4 from t1
5 group by id
ORA-00928: missing SELECT keyword
删除了重复数字式这样的。SQL> with t1 as (select id,type,qty,row_number()over(partition by id order by type desc) rn from tb_row_col )
2 select id,min(decode(rn,1,type,0)),min(decode(rn,1,QTY,0)),min(decode(rn,2,type,0)),min(decode(rn,2,QTY,0)),
3 min(decode(rn,3,type,0)),min(decode(rn,3,QTY,0)),min(decode(rn,4,type,0)),min(decode(rn,4,QTY,0))
4 from t1
5 group by id
6 /
Warning: connection was lost and re-established