CODEWITHSUNDEEP
google-bigquery
Peter
Peter

Reputation: 21

Transpose rows into columns in BigQuery

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!

Upvotes: 1

Views: 1806

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

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

Upvotes: 2

Related Questions