We recently had a need to unpivot two columns with item id’s in them so they’d come out as a single column with distinct values. Some of the entries had <null> as the parent id, and null should not appear in the output.
One could solve this with a simple UNION, but the view we were using was unfortunately rather expensive to run, so iterating it twice for the UNION was not an option.
So here goes:
SELECT DISTINCT
UNNEST(
CASE
WHEN parent_item_id IS NULL THEN array[item_id]
ELSE array[item_id, parent_item_id]
END
) AS item_id
FROM rather_expensive_view
WHERE
owner_id = 123456
In short, UNNEST unpivots arrays to rows, and we avoid printing NULL as an item_id when parent_item_id is NULL by not including it in the array in the CASE statement. The result is one iteration of the view, and a pretty list of item_ids.
To visualize the operation:
We go from:
---
item_id item_parent_id
10 NULL
11 4
12 3
---
To:
---
item_id
3
4
10
11
12
---
No NULL in the output :D
Adding additional columns to print, along with the UNNEST is fine. The additional columns will be duplicated for both rows in the case where an item has a parent. The output can, of course, also be joined with something like a sales table, so find sales of each item, including the parents.