PIVOT of non existing key value in SQL

Question

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'
  

Answer 1

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