Forum Stats

  • 3,750,345 Users
  • 2,250,159 Discussions
  • 7,866,944 Comments

Discussions

Pivot/unpivot

Dorian Grim
Dorian Grim Member Posts: 4 Red Ribbon
edited Mar 11, 2019 4:11AM in SQL & PL/SQL

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_nameunion all  select     'feb' date_id    ,a    ,b    ,c    ,feb as val  from     my_table_nameunion all  select     'mar' date_id    ,a    ,b    ,c    ,mar as val  from     my_table_nameunion all  select     'apr' date_id    ,a    ,b    ,c    ,apr as val  from     my_table_name)select * from main;

Thanks,

Dorian

Tagged:
Dorian Grimmathguy

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,887 Red Diamond
    edited Mar 6, 2019 10:42AM Accepted Answer

    Hi,

    3508532 wrote:Thanks for the reply. Sorry for the unconventional postI felt they can, but I can't get my head around the logic.

    Here's one way to use SELECT ... UNPIVOT:

    SELECT    a, b, c, date_id, valFROM      mainUNPIVOT  (    val         FOR  date_id  IN (jan, feb, mar, apr)         );
    Dorian GrimDorian Grim
«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,887 Red Diamond
    edited Mar 6, 2019 10:27AM

    Hi, Dorian,

    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
    It always helps to show what you've tried, that is, the code that seems to come closest to what you want.

    Always say which version of Oracle you're using (for example, 12.2.0.1.0).
    See the Forum FAQ:

    3508532 wrote:I need:abcjanfebmaraprabc11112111to look like:abcdate_idvalabcjan11abcfeb1abcmar12abcapr111Could someone please help me achieve this?Thanks,Dorian

    SELECT ... UNPIVOT can do that.

    I'm assuming jan, feb, mar, ... in your original table are all the same data type.  If not, you'll have to convert some of them before unpivoting.

    Dorian Grim
  • Dorian Grim
    Dorian Grim Member Posts: 4 Red Ribbon
    edited Mar 6, 2019 10:30AM

    Thanks for the reply. Sorry for the unconventional post

    I felt they can, but I can't get my head around the logic.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,292 Red Diamond
    edited Mar 6, 2019 10:30AM

    You mean like this?

    SQL> ed
    Wrote file afiedt.buf  1  with main as (
      2    select 'a' as a, 'b' as b, 'c' as c, 11 as jan, 1 as feb, 12 as mar, 111 as apr from dual
      3    )
      4  -- end of test data
      5  select a,b,c
      6        ,case level
      7          when 1 then 'jan'
      8          when 2 then 'feb'
      9          when 3 then 'mar'
    10          when 4 then 'apr'
    11        else null end as date_id
    12        ,case level
    13          when 1 then jan
    14          when 2 then feb
    15          when 3 then mar
    16          when 4 then apr
    17        else null end as val
    18  from main
    19* connect by level <= 4
    SQL> /A B C DAT        VAL
    - - - --- ----------
    a b c jan         11
    a b c feb          1
    a b c mar         12
    a b c apr        111
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,654 Black Diamond
    edited Mar 6, 2019 10:38AM

    Use row multiplier:

    WITH MULTIPLIER AS (

                        SELECT  LEVEL N

                          FROM  DUAL

                          CONNECT BY LEVEL <= 4

                       )

    SELECT  A,

            B,

            C,

            CASE N

              WHEN 1 THEN 'jan'

              WHEN 2 THEN 'feb'

              WHEN 3 THEN 'mar'

              WHEN 4 THEN 'apr'

            END DATE_ID,

            CASE N

              WHEN 1 THEN JAN

              WHEN 2 THEN FEB

              WHEN 3 THEN MAR

              WHEN 4 THEN APR

            END VAL

      FROM  YOUR_TABLE,

            MULTIPLIER

    /

    SY.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,654 Black Diamond
    edited Mar 6, 2019 10:39AM

    Now try it on multi-row table .

    SY.

    mathguy
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,887 Red Diamond
    edited Mar 6, 2019 10:42AM Accepted Answer

    Hi,

    3508532 wrote:Thanks for the reply. Sorry for the unconventional postI felt they can, but I can't get my head around the logic.

    Here's one way to use SELECT ... UNPIVOT:

    SELECT    a, b, c, date_id, valFROM      mainUNPIVOT  (    val         FOR  date_id  IN (jan, feb, mar, apr)         );
    Dorian GrimDorian Grim
  • mathguy
    mathguy Member Posts: 9,967 Gold Crown
    edited Mar 6, 2019 10:52AM

    Something like this. Note that if you must order by DATE_ID, you must use TO_DATE around it (I show one simple, perhaps unrealistic, example in the code below). Of course, you could instead use TO_DATE in the SELECT list (making the DATE_ID into a DATE column, rather than VARCHAR2) - then no further magic would be needed in ORDER BY.

    with  my_table_name (a, b, c, jan, feb, mar, apr) as (    select 'a', 'b', 'c',  11,  1,  12, 111 from dual union all    select 'A', 'x', 'z',  3,  8, 200,  15 from dual  )-- end of sample data; you don't need the WITH clause, use your actual table and column names below.select   a, b, c, date_id, valfrom     my_table_nameunpivot  (val for date_id in (jan as 'jan', feb as 'feb', mar as 'mar', apr as 'apr'))order by to_date(date_id, 'mon'), a, b, c  -- or whatever you need;A B C DATE_ID        VAL- - - ------- ----------A x z jan              3a b c jan             11A x z feb              8a b c feb              1A x z mar            200a b c mar             12A x z apr             15a b c apr            111
  • mathguy
    mathguy Member Posts: 9,967 Gold Crown
    edited Mar 6, 2019 11:05AM

    Regarding the MULTIPLIER (helper) view, of course the way you wrote it is correct, but perhaps the following variation is more intuitive.

    with  multiplier as (select column_value as date_id from sys.odcivarchar2list('jan', 'feb', 'mar', 'apr'))....

    - then the main query code can also be simplified. Earlier Oracle versions may need the TABLE( ) operator, but the idea is the same.

    Or - assuming, for example, that in the end DATE_ID will have to be of DATE data type, that can also be done in the MULTIPLIER view.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,292 Red Diamond
    edited Mar 6, 2019 11:36AM
    Solomon Yakobson wrote:Now try it on multi-row table .SY.

      C'mon Solomon you know the OP didn't ask for a multi-row table, he just asked for a single row to be unpivoted.

    Of course, you know I can do that... 

    SQL> ed
    Wrote file afiedt.buf  1  with main as (
      2      select 'a' as a, 'b' as b, 'c' as c, 11 as jan, 1 as feb, 12 as mar, 111 as apr from dual union all
      3      select 'd' as a, 'e' as b, 'f' as c, 22 as jan, 2 as feb, 24 as mar, 222 as apr from dual
      4      )
      5  -- end of test data
      6  select a,b,c
      7        ,case level
      8          when 1 then 'jan'
      9          when 2 then 'feb'
    10          when 3 then 'mar'
    11          when 4 then 'apr'
    12        else null end as date_id
    13        ,case level
    14          when 1 then jan
    15          when 2 then feb
    16          when 3 then mar
    17          when 4 then apr
    18        else null end as val
    19  from main
    20  connect by level <= 4
    21  and prior a = a
    22  and prior b = b
    23  and prior c = c
    24* and prior sys_guid() is not null
    SQL> /A B C DAT        VAL
    - - - --- ----------
    a b c jan         11
    a b c feb          1
    a b c mar         12
    a b c apr        111
    d e f jan         22
    d e f feb          2
    d e f mar         24
    d e f apr        2228 rows selected.

    LOL! 

  • mathguy
    mathguy Member Posts: 9,967 Gold Crown
    edited Mar 6, 2019 11:49AM

    Here you are assuming that the composite (a, b, c) (three columns) is unique, in the data if not by design. There is nothing in the OP's question to suggest that that's the case. You either need a PK, or you can use ROWID - or, best, use a cross join, as used to be done in the old days; no need for CONNECT BY in the main query, it can be used just to create the MULTIPLIER view in SY's answer. Much simpler and more efficient, and there are no complications.