0

I'm using the dataset bigquery-public-data.world_bank_global_population.population_by_country, and I'd like to convert this data into something along the lines of country, country_code, year, population.

country country_code year_1960 ... year_2018
World WLD 3032019978 ... 7594270356
Canada CAN 17909009 ... 37058856

to...

country country_code year population
World WLD 1960 3032019978
World WLD 2018 7594270356
Cananda CAN 1960 17909009
Canada CAN 2018 37058856

The trouble for me is there are 264 unique rows, and 60 'year' columns'.

I suspect there's a way to transpose the data without using 60 case when statements (I'm not even sure that many are allowed). I was able to get the results I was looking for using a couple of UDFs based on this post, but I'm hoping there's a more elegant way that doesn't rely on those.

user19462600
  • 21
  • 3
  • Does this answer your question? [How to unpivot in BigQuery?](https://stackoverflow.com/questions/27832170/how-to-unpivot-in-bigquery) – nbk Mar 13 '23 at 23:31
  • Previous my answer in the link tried not to use a dynamic sql. If you want a *elegant* way, you can consider *unpivot* query along with a *dynamic sql* – Jaytiger Mar 14 '23 at 00:32
  • @nbk the problem with a lot of the UNPIVOT documentation is that they use the same technique that I mentioned I wanted to avoid in the original question; listing out every field you want to pivot on. – user19462600 Mar 14 '23 at 01:03
  • @Jaytiger I found another solution similar to [this](https://stackoverflow.com/questions/71386894/dynamic-unpivot-in-bigquery) – user19462600 Mar 14 '23 at 01:05
  • Hi @user19462600, If your issue is resolved could you add resolution steps as the answer? – Prajna Rai T Mar 15 '23 at 13:41

0 Answers0