pstobiecki pstobiecki - 4 months ago 20
SQL Question

Convert BigQuery data from key-value to columns

Given some data in BigQuery in the following format:

| id | r.key | r.value |
+----+-------+---------+
| 1 | k1 | v11 |
| +-------+---------+
| | k2 | v12 |
| +-------+---------+
| | k3 | v13 |
+----+-------+---------+
| 2 | k1 | v21 |
| +-------+---------+
| | k2 | v22 |
| +-------+---------+
| | k3 | v23 |


how can I convert this data to something like:

| id | k1 | k2 | k3 |
+----+-----+-----+-----+
| 1 | v11 | v12 | v13 |
+----+-----+-----+-----+
| 2 | v21 | v22 | v23 |


?

Answer

Try below

SELECT
  id, 
  MAX(IF(r.key = 'k1', r.value, NULL)) AS k1,
  MAX(IF(r.key = 'k2', r.value, NULL)) AS k2,
  MAX(IF(r.key = 'k3', r.value, NULL)) AS k3      
FROM YourTable
GROUP BY id