SQL: how to pivot rows based on its value

Question

I want to convert this data:

id  value
---------
1   v1=10
1   v2=20
1   v3=30
2   v1=40

to look like this:

id  v1     v2     v3
---------------------------
1   10     20     30
2   40     null   null

Any Solution for this? I'd like to solve it bigquery's standard sql.

Answer 1

Below few options - all for BigQuery Standrad SQL

Option 1 - hardcoded pivoting

---

Assume you know in advance number and names of columns to create
In this case below does what you need

#standardSQL
SELECT 
  id, 
  MAX(IF(key = 'v1', val, NULL)) v1,
  MAX(IF(key = 'v2', val, NULL)) v2,
  MAX(IF(key = 'v3', val, NULL)) v3
FROM `project.dataset.table`, 
UNNEST([STRUCT<key STRING, val STRING>(SPLIT(value,"=")[OFFSET(0)], SPLIT(value, "=")[OFFSET(1)])])
GROUP BY id   

You can test, play with above using dummy data from your question as below

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, 'v1=10' value UNION ALL
  SELECT 1, 'v2=20' UNION ALL
  SELECT 1, 'v3=30' UNION ALL
  SELECT 2, 'v1=40' 
)
SELECT 
  id, 
  MAX(IF(key = 'v1', val, NULL)) v1,
  MAX(IF(key = 'v2', val, NULL)) v2,
  MAX(IF(key = 'v3', val, NULL)) v3
FROM `project.dataset.table`, 
UNNEST([STRUCT<key STRING, val STRING>(SPLIT(value,"=")[OFFSET(0)], SPLIT(value, "=")[OFFSET(1)])])
GROUP BY id
-- ORDER BY id   

result is as expected:

Row     id      v1      v2      v3   
1       1       10      20      30   
2       2       40      null    null       

Option 2 - dynamic pivoting

---

In case you you do not know number and names of columns - you will need first to generate script similar to one in above option #1

You should run below to get it

#standardSQL
SELECT CONCAT('SELECT id, ', 
   STRING_AGG(
      CONCAT('MAX(IF(key = "', key, '", val, NULL)) as ', key)
   ) 
   ,' FROM `project.dataset.table`, UNNEST([STRUCT<key STRING, val STRING>(SPLIT(value, "=")[OFFSET(0)], SPLIT(value, "=")[OFFSET(1)])]) GROUP BY id')
FROM (
  SELECT SPLIT(value, '=')[OFFSET(0)] key 
  FROM `project.dataset.table`
  GROUP BY key
)   

for example if you will run it against same dummy

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, 'v1=10' value UNION ALL
  SELECT 1, 'v2=20' UNION ALL
  SELECT 1, 'v3=30' UNION ALL
  SELECT 2, 'v1=40' 
)
SELECT CONCAT('SELECT id, ', 
   STRING_AGG(
      CONCAT('MAX(IF(key = "', key, '", val, NULL)) as ', key)
   ) 
   ,' FROM `project.dataset.table`, UNNEST([STRUCT<key STRING, val STRING>(SPLIT(value, "=")[OFFSET(0)], SPLIT(value, "=")[OFFSET(1)])]) GROUP BY id')
FROM (
  SELECT SPLIT(value, '=')[OFFSET(0)] key 
  FROM `project.dataset.table`
  GROUP BY key
) 

You will get below string

SELECT id, MAX(IF(key = "v1", val, NULL)) AS v1,MAX(IF(key = "v2", val, NULL)) AS v2,MAX(IF(key = "v3", val, NULL)) AS v3 FROM `project.dataset.table`, UNNEST([STRUCT<key STRING, val STRING>(SPLIT(value, "=")[OFFSET(0)], SPLIT(value, "=")[OFFSET(1)])]) GROUP BY id

So, now - if you run this script against your dummy data

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, 'v1=10' value UNION ALL
  SELECT 1, 'v2=20' UNION ALL
  SELECT 1, 'v3=30' UNION ALL
  SELECT 2, 'v1=40' 
)
SELECT id, MAX(IF(key = "v1", val, NULL)) AS v1,MAX(IF(key = "v2", val, NULL)) AS v2,MAX(IF(key = "v3", val, NULL)) AS v3 FROM `project.dataset.table`, UNNEST([STRUCT<key STRING, val STRING>(SPLIT(value, "=")[OFFSET(0)], SPLIT(value, "=")[OFFSET(1)])]) GROUP BY id   

you will get same result as in Option 1 - but not - now the final query was dynamically generated for you

Option 3 - flatten key - value vs. pivot

---

Pivoting is cool but for many practical cases below simple approach is found to be very useful and more appropriate for dealing with

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, 'v1=10' value UNION ALL
  SELECT 1, 'v2=20' UNION ALL
  SELECT 1, 'v3=30' UNION ALL
  SELECT 2, 'v1=40' 
)
SELECT 
  id, 
  SPLIT(value,"=")[OFFSET(0)] key, 
  SPLIT(value, "=")[OFFSET(1)] val
FROM `project.dataset.table`

which gives plain vanilla key-value flatten structure

Row     id      key     val  
1       1       v1      10   
2       1       v2      20   
3       1       v3      30   
4       2       v1      40   

Answer 2

SELECT
  id,
  MAX(IF(name = 'v1', value, NULL)) AS v1,
  MAX(IF(name = 'v2', value, NULL)) AS v2,
  MAX(IF(name = 'v3', value, NULL)) AS v3
FROM (
  SELECT
    SPLIT(value, '=')[OFFSET(0)] AS name,
    SPLIT(value, '=')[OFFSET(1)] AS value
  FROM dataset.table
)
GROUP BY id