Transpose rows into columns in BigQuery

Question

Hello BigQuery Community!

I have the following table in BigQuery:Table1: Output Table BigQuery

Now I want to transpose it to this format: Table2: Transpose Table BigQuery

You can also see the tables in this Google Sheet: https://docs.google.com/spreadsheets/d/1LF8OMql3BdBAReUX33Y2f3QMD2EW6Exw5hBOFm7EteU/edit?usp=sharing

The challenge is: the entities are always different and every search_term can contain up to X entities. I really dont know where to begin. Would be great, if someone could help me. I am more than happy about every help.

Thanks a lot!

Please put the input/expected output inline with your question, preferably as formatted text instead of as images. — Elliott Brossard, Nov 28 '17 at 17:14

score 2 · Answer 1 · answered Nov 28 '17 at 18:45

I really dont know where to begin

Below can be good start for you

#standardSQL
WITH `project.dataset.table1` AS (
  SELECT 'van rental london' search_term, 'Rental' entity, 'rental' entitydetail, 3 enittyCount UNION ALL
  SELECT 'van rental london', 'Location', 'london', 3 UNION ALL
  SELECT 'van rental london', 'Car Type', 'van', 3 UNION ALL
  SELECT 'van hire sheffield', 'Location', 'sheffield', 3 UNION ALL
  SELECT 'van hire sheffield', 'Car Type', 'van', 3 UNION ALL
  SELECT 'van hire sheffield', 'Hire', 'hire', 3 UNION ALL
  SELECT 'van hire', 'Car Type', 'van', 2 UNION ALL
  SELECT 'van hire', 'Hire', 'hire', 2 UNION ALL
  SELECT 'san diego car rental', 'Rental', 'rental', 3 UNION ALL
  SELECT 'san diego car rental', 'Location', 'San Diego', 3 UNION ALL
  SELECT 'san diego car rental', 'Vehicle Type', 'car', 3 UNION ALL
  SELECT 'long term car rental london', 'Vehicle Type', 'car', 4 UNION ALL
  SELECT 'long term car rental london', 'Location', 'london', 4 UNION ALL
  SELECT 'long term car rental london', 'Rental', 'rental', 4 UNION ALL
  SELECT 'long term car rental london', 'Long Term', 'Long Term', 4 UNION ALL
  SELECT 'london car hire', 'Location', 'london', 3 UNION ALL
  SELECT 'london car hire', 'Vehicle Type', 'car', 3 UNION ALL
  SELECT 'london car hire', 'Hire', 'hire', 3 
)
SELECT
  search_term,
  MAX(IF(pos=1, entity, NULL)) entity1, 
  MAX(IF(pos=1, entitydetail, NULL)) entitydetail1, 
  MAX(IF(pos=2, entity, NULL)) entity2, 
  MAX(IF(pos=2, entitydetail, NULL)) entitydetail2, 
  MAX(IF(pos=3, entity, NULL)) entity3, 
  MAX(IF(pos=3, entitydetail, NULL)) entitydetail3, 
  MAX(IF(pos=4, entity, NULL)) entity4, 
  MAX(IF(pos=4, entitydetail, NULL)) entitydetail4 
FROM (
  SELECT 
    search_term, entity, entitydetail, 
    ROW_NUMBER() OVER(PARTITION BY search_term ORDER BY entity) pos
  FROM `project.dataset.table1`
)
GROUP BY search_term

every search_term can contain up to X entities

Above query assumes that you know max of X, so you need to repeat below two lines in your query X times, replacing N with respective number

  MAX(IF(pos=N, entity, NULL)) entityN, 
  MAX(IF(pos=N, entitydetail, NULL)) entitydetailN,

Of course this can be easily generated using script (you can use BQ or any language of your choice for this)

You can also check https://stackoverflow.com/a/40766540/5221944 for more on this

Thanks a lot and thanks again for help. Works perfectly:-) – Peter Nov 29 '17 at 14:23 — Peter, Nov 29 '17 at 14:23

Transpose rows into columns in BigQuery

1 Answers1