sqloraclepivotazure-databricks

PIVOT of non existing key value in SQL


I have a table

test_table
-------------------
key      value       
'a'      'apple'     
'b'      'ball'
'c'       null
'd'       ''

I need to PIVOT in below way ! Key for e and f are not present in test table but I want e and f present in pivot table with value not available

I want to pivot a,b,c,d,e,f but any key value could be missing in this case e and f are missing

 pivot_table
 ------------------------------------
    a        b       c      d      e         f
  'apple'  'ball'  null          'N/A'     'N/A'
  

Solution

  • Rather than using PIVOT, you can get the same effect using conditional aggregation to check if there are 0 matching rows for each pivot key and return your default value else return the pivot value:

    SELECT CASE COUNT(CASE key WHEN 'a' THEN 1 END)
           WHEN 0
           THEN 'N/A'
           ELSE MAX(CASE key WHEN 'a' THEN value END)
           END AS a,
           CASE COUNT(CASE key WHEN 'b' THEN 1 END)
           WHEN 0
           THEN 'N/A'
           ELSE MAX(CASE key WHEN 'b' THEN value END)
           END AS b,
           CASE COUNT(CASE key WHEN 'c' THEN 1 END)
           WHEN 0
           THEN 'N/A'
           ELSE MAX(CASE key WHEN 'c' THEN value END)
           END AS c,
           CASE COUNT(CASE key WHEN 'd' THEN 1 END)
           WHEN 0
           THEN 'N/A'
           ELSE MAX(CASE key WHEN 'd' THEN value END)
           END AS d,
           CASE COUNT(CASE key WHEN 'e' THEN 1 END)
           WHEN 0
           THEN 'N/A'
           ELSE MAX(CASE key WHEN 'e' THEN value END)
           END AS e,
           CASE COUNT(CASE key WHEN 'f' THEN 1 END)
           WHEN 0
           THEN 'N/A'
           ELSE MAX(CASE key WHEN 'f' THEN value END)
           END AS f
    FROM   test_table
    

    Which, for the sample data:

    CREATE TABLE test_table (key, value) AS
    SELECT 'a', 'apple' FROM DUAL UNION ALL
    SELECT 'b', 'ball'  FROM DUAL UNION ALL
    SELECT 'c', NULL    FROM DUAL UNION ALL
    SELECT 'd', ''      FROM DUAL;
    

    Note: In Oracle, an empty string '' is identical to NULL so your d column will never have '' inserted, it will contain NULL the same as c.

    Outputs:

    A B C D E F
    apple ball null null N/A N/A

    fiddle