0

I have a table like:

YEAR_WEEK CHANNEL_A CHANNEL_B CHANNEL_C ...
2022-11 40 10 5 ...
2022-12 50 20 6 ...
2022-13 60 30 7 ...

I need to pivot the data, so that the table is shown as:

CHANNEL 2022-11 2022-12 2022-13 ...
CHANNEL_A 40 50 60 ...
CHANNEL_B 10 20 30 ...
CHANNEL_C 5 6 7 ...

Problem is that my database has no PIVOT() function. I found this guide (https://docs.exasol.com/db/latest/migration_guides/oracle/execution/sqltranslationguidelines.htm), showing how to rebuild the PIVOT() function from Oracle in Exasol but I don't understand it nor can I successfully apply it to my table.

Stu
  • 30,392
  • 6
  • 14
  • 33
Vega
  • 2,661
  • 5
  • 24
  • 49
  • Check this answer out. There are method using aggregate functions or subqueries https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server/15745076#15745076 – ekochergin Nov 23 '22 at 10:36
  • For the dynamic SQL solution: Exasol has no DECLARE. For the aggregate solution: Exasol returns "Feature not supported: VALUE". The JOIN solution - I don't know how many rows it will have. So no solution from that thread is useable sadly. – Vega Nov 24 '22 at 10:05
  • That's sad. Are there anywhere online playground for Exasol like db fiddle? – ekochergin Nov 24 '22 at 15:14

1 Answers1

0

It seems that you want to rather transpose a table.

You may want to first build a subquery of the form (YEAR_WEEK, CHANNEL_LABEL, VALUE) using, say, UNION ALL and then do the pivoting via Oracle to Exasol Migration.

Alexander Lipatiev
  • 11
  • 1
  • 4