Transposing in Standard SQL - BigQuery

Question

I would like know how to transpose rows into columns using BigQuery's Standard SQL.

I have read some post about this, but most of them use BigQuery's legacy function "GROUP_CONCAT_UNQUOTED", which does not exist in the Standard SQL version.

This is my data structure:

This is what I would to achieve:

Thanks for your help.

Google "pivot query SQL." If the number of keys is known and fixed, you don't need any heavy firepower from your SQL engine, just a pivot query. — Tim Biegeleisen, Mar 27 '18 at 06:38

score 7 · Accepted Answer · answered Mar 27 '18 at 06:45

7

If the number of keys are known and fixed, then a simple pivot query should work:

SELECT
    id,
    MAX(CASE WHEN key = 'technician' THEN value END) AS technician,
    MAX(CASE WHEN key = 'supervisor' THEN value END) AS supervisor,
    MAX(CASE WHEN key = 'location'   THEN value END) AS location
FROM yourTable
GROUP BY id;

answered Mar 27 '18 at 06:45

Tim Biegeleisen

502,043
27
286
360

The solution is working perfectly. Thanks for your help. – Milton Mar 27 '18 at 06:51

score 1 · Answer 2 · answered Jul 15 '19 at 05:05

Yet another solution - different logic, different syntax:

#standardSQL
SELECT id, 
  JSON_EXTRACT_SCALAR(value, '$.technician') technician,
  JSON_EXTRACT_SCALAR(value, '$.supervisor') supervisor,
  JSON_EXTRACT_SCALAR(value, '$.location') location
FROM (
  SELECT id, CONCAT('{', STRING_AGG(CONCAT('"', key, '":"', value, '"')), '}') value
  FROM `project.dataset.table`
  GROUP BY id
)

I doubt it has much extra value (or even at all) for this specific question, but I found this direction useful in some scenarios.

score 0 · Answer 3 · answered Jul 15 '19 at 02:07

Another solution - same logic, just a different syntax:

select
    id,
    MAX(if(key = 'technician', value, null)) AS technician,
    MAX(if(key = 'supervisor', value, null)) AS supervisor,
    MAX(if(key = 'location', value, null)) AS location
from `dataset.yourTable`
group by id

Transposing in Standard SQL - BigQuery

3 Answers3