请问oracle多列转多行

Question

请问大家本来是这样

要转成

但是只有一行，怎么把五列都转出来
现在是这样写的
SUM(CASE WHEN xxx='000' THEN eee ELSE null END) as "WWW"
但其实就不用sum，都是查好的，就是要转成竖的，
不知道怎么弄了
换了一个方法，但是报错，麻烦帮忙看下

 select * from(
 select t.jidi,r.caishou as xx from
(select f.FNETWEIGHT as caishou,g.FBASEADDRESSID from T_CUS_CollectingstatisticsE f,T_CUS_Collectingstatistics g where f.FPARENTID = g.fid 
and to_char(f.FPICKYTIME,'yyyy-mm-dd') like '2017%') r  left join
(select a.fid,a.FNUMBER,a.FDISPLAYNAME_L2  as jidi from T_BD_GeneralAsstActTypeGroup b,T_BD_GeneralAsstActType a where  b.FNUMBER='NSB001' and a.FGROUPID = b.FID)t on t.fid = r.FBASEADDRESSID) pivot (sum(xx) for jidi in (''aa'),aa,'bb',bb);

Answer 1

回复wjy407199: 代码长是必须的,因为你的字段多,但是结果应该没有问题.
--造数据(就以3行为例)
with temp as
(select '超越-礼县' jidi,'1514' jdbh,null caizhaiarea,null jindu,null weight,null muchan from dual
union all
select '陕果-青岛' jidi,'1203' jdbh,'2000' caizhaiarea,'#######' jindu,'100' weight,'0.05' muchan from dual
union all
select '陕果-灵台' jidi,'1301' jdbh,null caizhaiarea,null jindu,'11811' weight,null muchan from dual
)
select * from temp;
JIDI JDBH CAIZHAIA JINDU WEIGHT MUCHAN

---

超越-礼县 1514
陕果-青岛 1203 2000 ####### 100 0.05
陕果-灵台 1301 11811

with temp as
(select '超越-礼县' jidi,'1514' jdbh,null caizhaiarea,null jindu,null weight,null muchan from dual
union all
select '陕果-青岛' jidi,'1203' jdbh,'2000' caizhaiarea,'#######' jindu,'100' weight,'0.05' muchan from dual
union all
select '陕果-灵台' jidi,'1301' jdbh,null caizhaiarea,null jindu,'11811' weight,null muchan from dual
)
select 'JIBH' type,
max(decode(jidi,'超越-礼县',jdbh,null)) "超越-礼县",
max(decode(jidi,'陕果-青岛',jdbh,null)) "陕果-青岛",
max(decode(jidi,'陕果-灵台',jdbh,null)) "陕果-灵台"
from temp
union all
select 'CAIZHAIAREA' type,
max(decode(jidi,'超越-礼县',caizhaiarea,null)) "超越-礼县",
max(decode(jidi,'陕果-青岛',caizhaiarea,null)) "陕果-青岛",
max(decode(jidi,'陕果-灵台',caizhaiarea,null)) "陕果-灵台"
from temp
union all
select 'JINDU' type,
max(decode(jidi,'超越-礼县',jindu,null)) "超越-礼县",
max(decode(jidi,'陕果-青岛',jindu,null)) "陕果-青岛",
max(decode(jidi,'陕果-灵台',jindu,null)) "陕果-灵台"
from temp
union all
select 'WEIGHT' type,
max(decode(jidi,'超越-礼县',weight,null)) "超越-礼县",
max(decode(jidi,'陕果-青岛',weight,null)) "陕果-青岛",
max(decode(jidi,'陕果-灵台',weight,null)) "陕果-灵台"
from temp
union all
select 'MUCHAN' type,
max(decode(jidi,'超越-礼县',muchan,null)) "超越-礼县",
max(decode(jidi,'陕果-青岛',muchan,null)) "陕果-青岛",
max(decode(jidi,'陕果-灵台',muchan,null)) "陕果-灵台"
from temp;

TYPE 超越-礼县 陕果-青岛 陕果-灵台

---

JIBH 1514 1203 1301
caizhaiarea 2000
jindu #######
weight 100 11811
muchan 0.05