Pivot/unpivot

BluShadow

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

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

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