I have a Biguery table like this:
| Product | Date | Event |
|---|---|---|
| A | 2022-03-08 | M |
| A | 2022-03-25 | P |
| A | 2022-02-03 | S |
| B | 2022-02-20 | Q |
| B | 2022-03-10 | R |
Based on current date (2022-03-29), I need to insert in another Bigquery table in this format:
| Product | Month-0 | Month-1 |
|---|---|---|
| A | M;P | S |
| B | R | Q |
I appreciate if you can help me.
Regards.
CodePudding user response：
using pivot and string_agg will do this aggregation
With tbl as
(SELECT "A" Product , date "2022-03-08" date, "M" event
Union ALL SELECT "A", "2022-03-25", "P"
Union ALL SELECT "A", "2022-02-03", "S"
Union ALL SELECT "B", "2022-02-20", "Q"
Union ALL SELECT "B", "2022-03-10", "R"
)
SELECT *,
from
(SELECT Product,event, date_diff(current_date(),date,month) as diff
from tbl)
PIVOT(string_agg(event,";") month FOR diff IN (0,1,2) )
CodePudding user response：
Consider below approach
execute immediate (select '''
select * from your_table
pivot(string_agg(event,";") month for date_diff(current_date(),date,month) in (''' || string_agg('' || value) || '''))
'''
from (
select max(date_diff(current_date(),date,month)) max_diff from your_table
), unnest(generate_array(0, max_diff)) value
)
if applied to sample data in your question - output is
