ORDS, SODA & JSON in the Database

Hi, @hayxing
The number of columns in the output, and their names, must be hard-coded into the query. If you want the number of columns to depend on what data is actually found, then you need dynamic SQL.
If you know you have data for 2015-2017, but you're not sure about 2018-2019, you could simply write a query that pivots all five years, 2015-2019. If there doesn't happen to be any data for some of those years, that won't cause any error; the columns for the extra years will just have NULLs.
Instead of dynamic SQL, you could write a static query that produces a big VARCHAR2 column, formatted so it looks like a variable number of columns. For an example, see PIVOT SQL — oracle-tech

Hi,
I have tried with PIVOT / UNPIVOT and with the LISTAGG to for the FY just never get it right.
That should work. Post your code. It's hard to say what you did wrong without knowing what you did.

with sample_sales_table
as
  (
  select 2015 fy, 100 shoe, 50 shirt, 2 hat from dual
  union all
  select 2016, 120, 55, 4 from dual
  union all
  select 2017, 150, 80, 10 from dual
  )
select *
from sample_sales_table
unpivot (
  col_val
  for category in (
  shoe, shirt, hat
  )
)
pivot (
  max(col_val)
  for fy in (LISTAGG(DISTINCT fy, ',')) -- this part return error ORA-00934: group function is not allowed here
)
/

I am thinking of creating a view how you guys do that with dynamic sql ?

Thank you

Hi, @hayxing
for fy in (LISTAGG(DISTINCT fy, ',')) -- this part return error ORA-00934: group function is not allowed here
Oh no, that won't work. Oracle needs to know hw many columns there will be when it compiles the query. You need to hard-code the list of values, unless you're getting XML output. (If you get XML output, then the query always produces 1 column, a big XML object, that has a variable number of td elements. Earlier, I posted a link to another thread, that shows how to use the same approach with a single big VARCHAR2 column.)
I am thinking of creating a view how you guys do that with dynamic sql ?
I don't do that. I use a VARCHAR2 column formatted to look like multiple columns.
Earlier today, @solomon-yakobson posted a dynamic pivot solution: Convert columns into row of a table — oracle-tech Maybe you could do something like that.