Forum Stats

  • 3,851,618 Users
  • 2,264,003 Discussions
  • 7,904,791 Comments

Discussions

Pivot/unpivot

2»

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 42,305 Red Diamond
    edited Mar 7, 2019 3:13AM
    mathguy wrote: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.

    Thanks for telling me that, I was completely clueless about it.    Duh!  Of course I know that... but the OP hasn't specified a primary key, so I've assumed one to illustrate the point Solomon was making.  As for whether a connect by clause or a cross join is more appropriate actually depends on the real requirements.. of which we don't know, so there's actually no harm in illustrating alternative solutions.

  • mathguy
    mathguy Member Posts: 10,670 Blue Diamond
    edited Mar 7, 2019 8:39AM

    What you know will not help a future reader of this thread, if you don't write it in the answer. They can't read your mind.

    "connect by" clause vs. cross join for unpivoting...  I'll let you believe what you wish.

  • Dorian Grim
    Dorian Grim Member Posts: 4 Red Ribbon
    edited Mar 11, 2019 4:11AM

    Thank you guys for the nice replies.

    The correct answer has to go to Frank for the cleanliness of the unpivot.

    Here's a little insight. I applied the unpivot to my production data and got

    ORA-00918: column ambiguously defined

    The same error as described here Unpivot : Column Ambiguosuly defined

    I found out that you must not use the name of a column in your table in the for clause

    val for date_id in (JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC) -- in my case date_id is an already existing column in my table-- as is the activity_label column in the example thread