oracle数据竖列转横向显示问题！
0
悬赏园豆：50
[已解决问题]
解决于 2016-09-21 16:32
| SERTIAL | PROCESS_SERTIAL | PLANSTATUS |
| 1 | 1 | √ |
| 1 | 2 | √ |
| 1 | 3 | √ |
| 2 | 1 | √ |
| 2 | 2 | √ |
| 2 | 3 | √ |
| 2 | 4 | √ |
| 3 | 1 | √ |
| 3 | 2 | √ |
| 3 | 3 | √ |
以上是查询的数据结果，现在我需要转化为以下结果。
| SERIAL | 序号1 | 序号2 | 序号3 | 序号4 |
| 1 | √ | √ | √ | |
| 2 | √ | √ | √ | √ |
| 3 | √ | √ | √ |
说明：SERITAL过滤重复值作为行，PROCESS_SERTAL的最大值作为列，PLANSTATUS作为tab内容，其实就是将PROCESS_SERTAL和PLANSTATUS两个字段转换为横向方式显示。
这问题困扰了两天了，望各位大侠帮我解决问题，小弟先行谢过了！
最佳答案
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
不动声色就写上了😁
谢谢你回答我的问题。我第一个表格的数据是用这段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:
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
你试试这个
@刘宏玺: 在then 这个位置报错，我用的是PL/SQL
@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
@刘宏玺: 可以了，非常非常感谢，haha~
其他回答(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
用Group by分组就不行了，提示：不是group by 分组表达式