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!

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
385 views