CODEWITHSUNDEEP
sqloracle-databasepivot
daimne
daimne

Reputation: 67

PIVOT for value by reported date in Oracle SQL

I'm trying to use pivot to accomplish the following:

The data in the source table looks like:

Company Company ID Product Code Product Unit Reporting Year Value
a 1 dd gln 2019 7
a 1 dd gln 2020 2
b 2 bb kg 2021 3

The data in the pivoted table should look like:

Company Company ID Product Code Product Unit 2019 2020 2021
a 1 dd gln 7 2
b 2 bb kg 3

I tried and it did not work:

select * from source pivot ( max(value) for year in (2019,2020,2021) );

Any help greatly appreciated.

Upvotes: 0

Views: 778

Answers (4)

user15746603
user15746603

Reputation:

You were almost there:

select * from source pivot ( max(Value) for "Reporting Year" in (2019,2020,2021) );

Please note that Oracle does not allow column names with spaces, so the space there should be replaced with an underscore, for example.

Thus, the query would be:

select * from source pivot ( max(Value) for "Reporting_Year" in (2019,2020,2021) );

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65433

No need to specify all the years within the PIVOT clause's list by using a dynamical code structure , assuming that you have only those three year values within the table currently, and propagate to the new pivoted columns along with the newly inserted upcoming year values through creating a stored function such as

CREATE OR REPLACE FUNCTION Fn_Pivot_Table RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  v_sql       VARCHAR2(32767);
  v_cols      VARCHAR2(32767);   
BEGIN
  SELECT LISTAGG( ReportingYear  , ',') WITHIN GROUP ( ORDER BY ReportingYear )                 
    INTO v_cols
    FROM ( SELECT DISTINCT ReportingYear FROM source );
      
  v_sql :='SELECT * 
             FROM source
            PIVOT(
                   MAX(Value) FOR ReportingYear IN (' || v_cols ||' ) 
                  ) 
            ORDER BY CompanyID';

  OPEN v_recordset FOR v_sql;
  RETURN v_recordset;
END;
/

which might be invoked from the SQL Developer's console as

SQL> DECLARE
    result SYS_REFCURSOR;
BEGIN
   :result := Fn_Pivot_Table;
END;
/

SQL> PRINT result;

Upvotes: 1

MT0
MT0

Reputation: 168796

Change year to reportingyear and your code appears to work:

select * from source pivot ( max(value) for reportingyear in (2019,2020,2021) );

Which, for the sample data:

CREATE TABLE source ( Company, CompanyID, ProductCode, ProductUnit, ReportingYear, Value ) AS
SELECT 'a', 1, 'dd', 'gln', 2019, 7 FROM DUAL UNION ALL
SELECT 'a', 1, 'dd', 'gln', 2020, 2 FROM DUAL UNION ALL
SELECT 'b', 2, 'bb', 'kg', 2021, 3 FROM DUAL

Outputs:

COMPANY COMPANYID PRODUCTCODE PRODUCTUNIT 2019 2020 2021
b 2 bb kg <null> <null> 3
a 1 dd gln 7 2 <null>

If you are using quoted identifiers and have a space in the column name then you need to use a quoted identifier every time you refer to that column:

select * from source pivot ( max(value) for "Reporting Year" in (2019,2020,2021) );

db<>fiddle here

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271191

Just use conditional aggregation:

select Company, Company_ID, Product_Code, Product_Unit,
       sum(case when year = 2019 then value else 0 end) as val_2019,
       sum(case when year = 2020 then value else 0 end) as val_2020,
       sum(case when year = 2021 then value else 0 end) as val_2021
from source
group by Company, Company_ID, Product_Code, Product_Unit;

Upvotes: 1

Related Questions