首页新闻找找看学习计划

oracle数据竖列转横向显示问题!

0
悬赏园豆:50 [已解决问题] 解决于 2016-09-21 16:32
SERTIAL PROCESS_SERTIAL PLANSTATUS
    1  1  √
    1   √
    1   √ 
    2   √ 
    2   √ 
    2   √ 
    2   √ 
   3   √ 
   3   √ 
   3   √ 

以上是查询的数据结果,现在我需要转化为以下结果。

SERIAL 序号1 序号2 序号3 序号4
1  
2
3  

说明:SERITAL过滤重复值作为行,PROCESS_SERTAL的最大值作为列,PLANSTATUS作为tab内容,其实就是将PROCESS_SERTAL和PLANSTATUS两个字段转换为横向方式显示。

 

这问题困扰了两天了,望各位大侠帮我解决问题,小弟先行谢过了!

 

m869485074的主页 m869485074 | 初学一级 | 园豆:84
提问于:2016-09-21 11:07
< >
分享
最佳答案
0

select distinct SERTIAL,

(select PLANSTATUS feom table where SERTIAL = t.SERTIAL and PROCESS_SERTIAL = 1) 序号1,

(select PLANSTATUS feom table where SERTIAL = t.SERTIAL and PROCESS_SERTIAL = 2) 序号2,

(select PLANSTATUS feom table where SERTIAL = t.SERTIAL and PROCESS_SERTIAL = 3) 序号3,

(select PLANSTATUS feom table where SERTIAL = t.SERTIAL and PROCESS_SERTIAL = 4) 序号4

from table t

收获园豆:40
刘宏玺 | 专家六级 |园豆:14004 | 2016-09-21 15:08

不动声色就写上了😁

JaneEyre | 园豆:454 (菜鸟二级) | 2016-09-21 15:11

谢谢你回答我的问题。我第一个表格的数据是用这段sql查出来的:

select t.cardno,t.serial,t.process_serial,decode(t.planstatus,2,'√','') as planstatus
from MT_PL_ORDER_PRODUCT_CARD_H t
where t.cardno in (select t.cardno
from MT_PL_ORDER_PRODUCT_CARD_M t
where t.process_ver = '1606067-1_1')
order by t.cardno,t.process_serial。

我刚刚按照你的方式写了,不知道是否我写错了,感觉行不通。技术比较菜,望谅解!

m869485074 | 园豆:84 (初学一级) | 2016-09-21 15:52

@m869485074: 

select SERTIAL,

max(case when PROCESS_SERTIAL = 1 then PLANSTATUS end) 序号1,

max(case when PROCESS_SERTIAL = 2 then PLANSTATUS end) 序号2,

max(case when PROCESS_SERTIAL = 3 then PLANSTATUS end) 序号3,

max(case when PROCESS_SERTIAL = 4 then PLANSTATUS end) 序号4

from (select t.cardno,t.serial,t.process_serial,decode(t.planstatus,2,'√','') as planstatus
from MT_PL_ORDER_PRODUCT_CARD_H t
where t.cardno in (select t.cardno
from MT_PL_ORDER_PRODUCT_CARD_M t
where t.process_ver = '1606067-1_1')
order by t.cardno,t.process_serial) t

group by SERTIAL

 

你试试这个

刘宏玺 | 园豆:14004 (专家六级) | 2016-09-21 16:00

@刘宏玺: 在then 这个位置报错,我用的是PL/SQL

m869485074 | 园豆:84 (初学一级) | 2016-09-21 16:16

@m869485074:

select SERTIAL,

case when count(case when PROCESS_SERTIAL = 1 and PLANSTATUS = 2  then 1 else null end) > 0 then '√' else '' end 序号1,

case when count(case when PROCESS_SERTIAL = 2 and PLANSTATUS = 2  then 1 else null end) > 0 then '√' else '' end  序号2,

case when count(case when PROCESS_SERTIAL = 3 and PLANSTATUS = 2  then 1 else null end) > 0 then '√' else '' end  序号3,

case when count(case when PROCESS_SERTIAL = 4 and PLANSTATUS = 2  then 1 else null end) > 0 then '√' else '' end  序号4

from (select t.cardno,t.serial,t.process_serial,t.planstatus as planstatus
from MT_PL_ORDER_PRODUCT_CARD_H t
where t.cardno in (select t.cardno
from MT_PL_ORDER_PRODUCT_CARD_M t
where t.process_ver = '1606067-1_1')
order by t.cardno,t.process_serial) t

group by SERTIAL

刘宏玺 | 园豆:14004 (专家六级) | 2016-09-21 16:25

@刘宏玺: 可以了,非常非常感谢,haha~

m869485074 | 园豆:84 (初学一级) | 2016-09-21 16:29
其他回答(1)
0

SELECT T.SERTIAL,
(CASE T.PROCESS_SERTIAL WHEN '1' THEN T.PLANSTATUS ELSE '' END) 序号1,
(CASE T.PROCESS_SERTIAL WHEN '2' THEN T.PLANSTATUS ELSE '' END) 序号2,
(CASE T.PROCESS_SERTIAL WHEN '3' THEN T.PLANSTATUS ELSE '' END) 序号3,
(CASE T.PROCESS_SERTIAL WHEN '4' THEN T.PLANSTATUS ELSE '' END) 序号4
FROM T
GROUP BY T.SERTIAL

收获园豆:10
Meng_meng | 园豆:67 (初学一级) | 2016-09-21 15:08

用Group by分组就不行了,提示:不是group by 分组表达式

支持(0) 反对(0) m869485074 | 园豆:84 (初学一级) | 2016-09-21 16:18
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册