Convert rows to columns in Bigquery-CodePudding

I want to convert the below data into the output. Eg: There are 8 unique areas in the below table and an id can have max of only 4 areas of amount. Input table:

ID	Area	Amount
1	1234	101
1	2341	200
1	3214	100
2	1234	300
2	4321	100
2	2341	290
2	4351	222
3	3255	380
3	3214	190
3	5431	200
3	4444	100

Output table:

ID	Area1	Amount1	Area2	Amount2	Area3	Amount3	Area4	Amount4
1	1234	101	2341	200	3214	100	NULL	NULL
2	1234	300	4321	100	2341	290	4351	222
3	3255	380	3214	190	5431	200	4444	100

CodePudding user response：

Consider below approach

select * from (
  select *, row_number() over(partition by id) pos
  from your_table
) pivot (
  any_value(area) area, 
  any_value(amount) amount 
  for pos in (1,2,3,4)
)

if applied to sample data in your question - output is

CodePudding user response：

I would suggest "conditional aggregates" based on a row number you calculate with a derived table.

select
      id
    , max(case when rn = 1 then area end)   as area1
    , max(case when rn = 1 then amount end) as amount1
    , max(case when rn = 2 then area end)   as area2
    , max(case when rn = 2 then amount end) as amount2
    , max(case when rn = 3 then area end)   as area3
    , max(case when rn = 3 then amount end) as amount3
    , max(case when rn = 4 then area end)   as area4
    , max(case when rn = 4 then amount end) as amount4
from (
    select
          id, area, amount
         , row_number() over(partition by ID order by area) as rn
    from yourtable
    ) d