Forum Stats

  • 3,759,232 Users
  • 2,251,515 Discussions
  • 7,870,547 Comments

Discussions

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

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
    
    
    
  • 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.

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
    
    
    
  • 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

  • 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.

  • 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
    )
    /
    
    
  • HayXing
    HayXing Member Posts: 28 Blue Ribbon
  • 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.