replacing NULLs with custom values in Oracle SQL PIVOT results, when the rows are missing

user_6777650 0
Viewed 477

Pivot queries retun NULL when there's no row to be pivoted.

in the following example the COLOR attribute is missing for id=2.

with src_data (id, attr_name, attr_id, attr_type) as (
    select 1, 'ITALY', 'IT', 'COUNTRY' FROM DUAL UNION ALL --
    select 1, 'GREEN', 'G', 'COLOR' FROM DUAL UNION ALL --
    select 1, 'BIG', 'B', 'SIZE' FROM DUAL UNION ALL --
    select 2, 'FRANCE', 'FR', 'COUNTRY' FROM DUAL UNION ALL --
    select 2, 'SMALL', 'S', 'SIZE' FROM DUAL  --
)
select * from src_data
    PIVOT (MAX(ATTR_NAME) AS NAME, MAX(ATTR_ID) AS ID --
    FOR attr_type IN ('COUNTRY' AS "COUNTRY", 'COLOR' AS "COLOR", 'SIZE' AS "SIZE"));

the result is

ID COUNTRY_NAME COUNTRY_ID COLOR_NAME COLOR_ID SIZE_NAME SIZE_ID
1 ITALY IT GREEN G BIG B
2 FRANCE FR NULL NULL SMALL S

see dbfiddle

I would like to replace those null values with specific values (e.g. use N/D for the name and -1 for the id).

the naive attempts do not work

  • PIVOT (NVL(MAX(ATTR_NAME), 'N/D') AS NAME ... gives ORA-56902: expect aggregate function inside pivot operation
  • PIVOT (MAX(NVL(ATTR_NAME, 'N/D')) AS NAME ... still gives null results. My interpretation is that that NVL is never even called, since there's no row at all to be called on (where ATTR_TYPE = 'COLOR' AND ID = 2)

The very ugly solutions I see are

  • adding the specific NVL logic on all the columns produced by PIVOT. My real life case has 14 such columns.
  • adding fake rows to the PIVOT input to cover such cases

any better ideas?

--- edit ---

Looks like the native pivot cannot do this. The best I could do is adding the missing rows with an outer join and the having the nvl on the real NULLs returned by the outer join

with src_data (id, attr_name, attr_id, attr_type) as (
    select 1, 'ITALY', 'IT', 'COUNTRY' FROM DUAL UNION ALL
    select 1, 'GREEN', 'G', 'COLOR' FROM DUAL UNION ALL
    select 1, 'BIG', 'B', 'SIZE' FROM DUAL UNION ALL
    select 2, 'FRANCE', 'FR', 'COUNTRY' FROM DUAL UNION ALL
    select 2, 'SMALL', 'S', 'SIZE' FROM DUAL
),
     src_ids_types as (
         select src_ids.id, src_types.attr_type
         from (select distinct id from src_data) src_ids
                  cross join (select distinct attr_type from src_data) src_types
     ),
     full_data as (
         select sit.id, sit.attr_type, d.attr_name, d.attr_id
         from src_ids_types sit
                  left outer join src_data d on d.id = sit.id and d.attr_type = sit.attr_type
     )
select *
from full_data d
    PIVOT (MAX(NVL(ATTR_NAME, 'N/D')) AS NAME, MAX(NVL(ATTR_ID, -1)) AS ID --
    FOR attr_type IN ('COUNTRY' AS "COUNTRY", 'COLOR' AS "COLOR", 'SIZE' AS "SIZE"))

db-fiddle

pivot oracle sql
4 Answers

You can do this by:

  • Generating a list of all attr_types (works best if you have a table of these. You can generate a distinct list from the source data, but this could be slow on large data sets)
  • Outer joining the data to this with a partitioned outer join on id. This will give you a row for every attribute from the list above for every id
  • Convert the null names/ids to N/A, -1 as appropriate in a subquery
  • Passing the output to pivot

Which gives:

with src_data (id, attr_name, attr_id, attr_type) as (
  select 1, 'ITALY', 'IT', 'COUNTRY' FROM DUAL UNION ALL --
  select 1, 'GREEN', 'G', 'COLOR' FROM DUAL UNION ALL --
  select 1, 'BIG', 'B', 'SIZE' FROM DUAL UNION ALL --
  select 2, 'FRANCE', 'FR', 'COUNTRY' FROM DUAL UNION ALL --
  select 2, 'SMALL', 'S', 'SIZE' FROM DUAL  --
), attrs as (
  select distinct attr_type from src_data
), id_attrs as (
  select id, attr_type,
         nvl ( attr_name, 'N/A' ) attr_name,
         nvl ( attr_id, -1 ) attr_id
  from   attrs a
  left   join src_data d
    partition by ( id ) 
  using ( attr_type ) 
)
select * from id_attrs
pivot (
  max(attr_name) as name, max(attr_id) as id --
  for attr_type in (
    'COUNTRY' AS "COUNTRY", 'COLOR' AS "COLOR", 'SIZE' AS "SIZE"
  )
);

ID    COUNTRY_NAME   COUNTRY_ID   COLOR_NAME   COLOR_ID   SIZE_NAME   SIZE_ID   
    1 ITALY          IT           GREEN        G          BIG         B          
    2 FRANCE         FR           N/A          -1         SMALL       S
user_14859550

I would use the aggregate function with CASE WHEN to do the pivot.

with src_data (id, attr_name, attr_id, attr_type) as (
    select 1, 'ITALY', 'IT', 'COUNTRY' FROM DUAL UNION ALL --
    select 1, 'GREEN', 'G', 'COLOR' FROM DUAL UNION ALL --
    select 1, 'BIG', 'B', 'SIZE' FROM DUAL UNION ALL --
    select 2, 'FRANCE', 'FR', 'COUNTRY' FROM DUAL UNION ALL --
    select 2, 'SMALL', 'S', 'SIZE' FROM DUAL  --
)
SELECT ID,
       NVL(MAX(CASE WHEN attr_type = 'COUNTRY' THEN attr_name  END),'N/A') "COUNTRY_NAME",
       NVL(MAX(CASE WHEN attr_type = 'COUNTRY' THEN attr_id END),'N/A') "COUNTRY_ID",
       NVL(MAX(CASE WHEN attr_type = 'COLOR' THEN attr_name END),'N/A') "COLOR_NAME",
       NVL(MAX(CASE WHEN attr_type = 'COLOR' THEN attr_id END),'N/A') "COLOR_ID",
       NVL(MAX(CASE WHEN attr_type = 'SIZE' THEN attr_name END),'N/A') "SIZE_NAME",
       NVL(MAX(CASE WHEN attr_type = 'SIZE' THEN attr_id END),'N/A') "SIZE_ID"
FROM src_data
GROUP BY ID

sqlfiddle

user_51760710

If you want more resilient structure rather than writing multiple NVL()s manually, prefer creating a stored function which will return SYS_REFCURSOR as data type such as

CREATE OR REPLACE FUNCTION Pivot_Src_Data RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  v_sql       VARCHAR2(32767);
  v_cols1     VARCHAR2(32767);
  v_cols2     VARCHAR2(32767);  
BEGIN
  SELECT LISTAGG( 'NVL('||attr_type||'_'||typ||',''N/D'') AS "'||attr_type||'_'||typ||'"' , ',' )
          WITHIN GROUP ( ORDER BY attr_type ) 
    INTO v_cols1
    FROM ( SELECT DISTINCT attr_type, typ 
             FROM src_data
            CROSS JOIN (SELECT 'ID' AS typ FROM dual UNION ALL SELECT 'NAME' FROM dual) );

  SELECT LISTAGG( ''''||attr_type||''' AS "'||attr_type||'"' , ',' )
          WITHIN GROUP ( ORDER BY attr_type )
    INTO v_cols2
    FROM ( SELECT DISTINCT attr_type FROM src_data );
                
  v_sql := 'SELECT NVL(ID,-1) AS ID, '|| v_cols1 ||
           '  FROM src_data s
             PIVOT (MAX(attr_name) AS name, MAX(attr_id) AS id 
               FOR attr_type IN ('|| v_cols2 ||'))';

  OPEN v_recordset FOR v_sql;
  RETURN v_recordset;
END;
/

which will be called from the SQL Developer's console as

SQL> DECLARE
    res SYS_REFCURSOR;
BEGIN
   :res := Pivot_Src_Data;
END;
/

SQL> PRINT res;

this way, no matter how many new attribute types you add to the table, you'll see all of them within the pivoted result set, unless the length of the string presented as the argument for the LISTAGG() function exceeds the threshold value, which is 4000, for the function.

user_58413060
user_67776500
Related