SQL - Pivot

Updated: 2020-11-14

Most of SQL engines do not have built-in functions to convert rows into columns, (a.k.a "transpose" the table). In order to convert a table like this:

id   | key   | value
-----|-------|------------
id_0 | key_0 | value_1
id_0 | key_1 | value_10
id_1 | key_1 | value_100
id_2 | key_0 | value_1000

To this, so each key is a separate column:

id   | key_0      | key_1
-----|------------|------------
id_0 | value_1    | value_10
id_1 | null       | value_100
id_2 | value_1000 | null

This can be achieved by GROUP(by id) and ARRAY_AGG and filter out un-related info:

-- prep the table
WITH data AS (
  SELECT 'id_0' AS id, 'key_0' AS key, 'value_1' AS value
  UNION ALL SELECT 'id_0' AS id, 'key_1' AS key, 'value_10' AS value
  UNION ALL SELECT 'id_1' AS id, 'key_1' AS key, 'value_100' AS value
  UNION ALL SELECT 'id_2' AS id, 'key_0' AS key, 'value_1000' AS value
)

-- actual pivot logic
SELECT
  id,
  ARRAY_AGG(CASE key WHEN 'key_0' THEN value ELSE NULL END IGNORE NULLS)[SAFE_OFFSET(0)] AS key_0,
  ARRAY_AGG(CASE key WHEN 'key_1' THEN value ELSE NULL END IGNORE NULLS)[SAFE_OFFSET(0)] AS key_1
FROM data
GROUP BY 1

Where CASE key WHEN 'key_0' THEN value ELSE NULL END will nullify values not related to this key, and IGNORE NULLS will make sure only relevant values goes into ARRAY_AGG.