Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Pivot/unpivot

Dorian GrimMar 6 2019 — edited Mar 11 2019

I am on Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

======

I need:

create table my_table_name 

(

  a varchar2(255)

  ,b varchar2(255)

  ,c varchar2(255)

  ,jan int

  ,feb int

  ,mar int

  ,apr int

);

insert into (a, b, c, jan, feb, mar, apr) values ('a', 'b', 'c', 11, 1, 12, 111);

abcjanfebmarapr
abc11112111

to look like:

abcdate_idval
abcjan11
abcfeb1
abcmar12
abcapr111

Could someone please help me achieve this?

I know of UNION, but I don't want to use it in hopes there's something already there for me, built-in.

with main as

(

  select

    'jan' date_id

    ,a

    ,b

    ,c

    ,jan as val

  from

    my_table_name

union all

  select

    'feb' date_id

    ,a

    ,b

    ,c

    ,feb as val

  from

    my_table_name

union all

  select

    'mar' date_id

    ,a

    ,b

    ,c

    ,mar as val

  from

    my_table_name

union all

  select

    'apr' date_id

    ,a

    ,b

    ,c

    ,apr as val

  from

    my_table_name

)

select * from main;

Thanks,

Dorian

This post has been answered by Frank Kulash on Mar 6 2019
Jump to Answer

Comments

Post Details

Added on Mar 6 2019
13 comments
380 views