wjy407199
半城风月半城雪
2018-04-09 03:16
采纳率: 76.9%
浏览 2.6k

请问oracle多列转多行

请问大家本来是这样
图片说明
要转成
图片说明
但是只有一行,怎么把五列都转出来
现在是这样写的
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);
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • chen222246lei
    chen222246lei 2018-04-10 01:09
    已采纳

    回复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

    点赞 评论
  • qq_37461349
    不喜欢吃猫的鱼 2018-04-09 05:34

    你可以试下这个 通过用decode函数实现
    例如
    SELECT
    SUM(DECODE(ENAME,'SMITH',SAL,0)) SMITH,
    SUM(DECODE(ENAME,'ALLEN',SAL,0)) ALLEN,
    SUM(DECODE(ENAME,'WARD',SAL,0)) WARD,
    SUM(DECODE(ENAME,'JONES',SAL,0)) JONES,
    SUM(DECODE(ENAME,'MARTIN',SAL,0)) MARTIN FROM EMP

    输出结果如下
    SMITH ALLEN WARD JONES MARTIN
    800 1600 1250 2975 1250

    点赞 评论
  • qq_37461349
    不喜欢吃猫的鱼 2018-04-09 06:23

    要四列的话就只要四个sum就好了,数据多了你看下是不是有重复的 对结果group by一下

    点赞 评论
  • chen222246lei
    chen222246lei 2018-04-09 07:19

    你现在是一列的结果出来了, 剩下的一样呀, 只不过用union all将5个查询结果合起来不就成了. 就是说每个地区占5行.

    点赞 评论

相关推荐