Generate columns from rows automatically (without IF)

Question

My question is similar to this How to simulate a pivot table with BigQuery?. But imagine that you have unpredicted amount of rows, so you can't list them in IF statement. So is there any way to take DISTINCT values from one column and create list of columns from it?

Answer 1

Very simple example below just to show approach:

Assume you have temp.example table:

select * from
(select '2015-08-01' as day, 1 as category, 11 as volume),
(select '2015-08-01' as day, 2 as category, 21 as volume),
(select '2015-08-01' as day, 3 as category, 31 as volume),
(select '2015-08-02' as day, 1 as category, 101 as volume),
(select '2015-08-02' as day, 2 as category, 201 as volume),
(select '2015-08-03' as day, 1 as category, 301 as volume),
(select '2015-08-03' as day, 2 as category, 302 as volume),
(select '2015-08-03' as day, 4 as category, 304 as volume)

and, assume you want to build query like below but w/o knowing in advance how many distinct categories you have

select 
   day,
   sum(if(category = 1, volume, 0)) as category_1,
   sum(if(category = 2, volume, 0)) as category_2,
   sum(if(category = 3, volume, 0)) as category_3,
   sum(if(category = 4, volume, 0)) as category_4
from temp.example
group by day
order by day

Below is GBQ code that does exactly this

select 'select day, ' + 
   group_concat_unquoted('sum(if(category = ' + string(category) + ', volume, 0)) as category_' + string(category)) 
   + ' from temp.example group by day order by day'
from (select category from temp.example group by category order by category)

Output of this query is a query that will produce pivot for you if you run it

day          category_1  category_2 category_3   category_4  
2015-08-01           11          21         31            0  
2015-08-02          101         201          0            0  
2015-08-03          301         302          0          304  

Answer 2

Yes indeed it's possible but you need to use your own programming language, as you cannot generate SQL syntax using SQL query language.