Forum Stats

  • 3,759,247 Users
  • 2,251,515 Discussions
  • 7,870,551 Comments

Discussions

Categories

PIVOT and UNPIVOT

HayXing
HayXing Member Posts: 28 Blue Ribbon
edited Apr 19, 2021 5:38PM in SQL & PL/SQL

I have a table that contain years that and total sales like following 


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;

Source of origin table

	FY	 SHOE	   SHIRT	HAT
---------- ---------- ---------- ----------
      2015	  100	      50	  2
      2016	  120	      55	  4
      2017	  150	      80	 10

Possibly 2018, 2019 data

How to create following report ?


WITH sales_report
AS
  (
  select 'SHOE' AS CATEGORY, 100 AS "2015", 120 AS "2016", 150 AS "2017" from dual
  union all
  select 'SHIRT', 50, 55, 80 from dual
  union all
  select 'HAT', 2, 4, 10 from dual
  )
SELECT *
FROM sales_report;


Desired Report output as following:

CATEGORY	 2015	    2016       2017
---------- ---------- ---------- ----------
SHOE		  100	     120	150
SHIRT		   50	      55	 80
HAT		    2	       4	 10

The report may look like this too if new record is added

CATEGORY	 2015	    2016       2017	  2018
---------- ---------- ---------- ---------- ----------
SHOE		  100	     120	150	   180
SHIRT		   50	      55	 80	   120
HAT		    2	       4	 10	    15


I have tried with PIVOT / UNPIVOT and with the LISTAGG to for the FY just never get it right.

If someone can help


Thanks

Tagged:
· Share on TwitterShare on Facebook

Best Answers

  • Paulzip
    Paulzip Member Posts: 8,452 Blue Diamond
    edited Apr 19, 2021 6:00PM Accepted Answer 
    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 (2015, 2016, 2017)
)
/


CATEGORY|      2015|      2016|      2017
--------|----------|----------|----------
SHOE    |       100|       120|       150
SHIRT   |        50|        55|        80
HAT     |         2|         4|        10
    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond
    Accepted Answer

    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.

    · Share on TwitterShare on Facebook

Answers

  • Paulzip
    Paulzip Member Posts: 8,452 Blue Diamond
    edited Apr 19, 2021 6:00PM Accepted Answer 
    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 (2015, 2016, 2017)
)
/


CATEGORY|      2015|      2016|      2017
--------|----------|----------|----------
SHOE    |       100|       120|       150
SHIRT   |        50|        55|        80
HAT     |         2|         4|        10
    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond

    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

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond

    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.

    · Share on TwitterShare on Facebook
  • HayXing
    HayXing Member Posts: 28 Blue Ribbon
    edited Apr 20, 2021 12:07AM 
    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
)
/
    · Share on TwitterShare on Facebook
  • HayXing
    HayXing Member Posts: 28 Blue Ribbon
    https://community.oracle.com/tech/developers/discussion/comment/16792746#Comment_16792746

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

    · Share on TwitterShare on Facebook
  • HayXing
    HayXing Member Posts: 28 Blue Ribbon
    https://community.oracle.com/tech/developers/discussion/comment/16792744#Comment_16792744

    Thank you 👍️

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond
    Accepted Answer

    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.

    · Share on TwitterShare on Facebook