Transform a Bigquery Table with variable columns

Question

I am trying to create a pivot sort of thing for a group of customer ids (cids).The dataset is of the form

Cid	Match
A	1
A	2
B	2
B	3
B	4

The transformed dataset should look like

Cid	Match_1	Match_2	Match_3
A	1	1	null
B	2	3	4

Here the Match_n suffix is dynamic and should be decided based on the max rows present across all Cids. I am not sure if this is even feasible.

I have tried pivot and other things but eventually hit a wall.

Found this but this is for a specific range of i (Big Query Transpose).

P.S: I just thought of something! Maybe that works. But still currently need help.

`Match_2` at first row in your expected output shouldn't be `2` by any chance ? And order of column value is significant ? I mean For `B`, `3, 2, 4` or `4, 3, 2` is possible output? or you're just expecting them sorted ? — Jaytiger, Apr 26 '23 at 01:04

score 0 · Answer 1 · answered Apr 26 '23 at 03:03

Match_n suffix is dynamic and should be decided based on the max rows present across all Cids.

I think you should consider a dynamic SQL along with PIVOT query.

-- sample data
CREATE TEMP TABLE sample_table AS
SELECT Cid, Match 
  FROM UNNEST(SPLIT('AABBB','')) Cid WITH offset 
  JOIN UNNEST([1, 2, 2, 3, 4]) Match WITH offset USING(offset);

-- query starts here  
EXECUTE IMMEDIATE FORMAT("""
  SELECT * 
    FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Cid ORDER BY Match) rn FROM sample_table)
   PIVOT (ANY_VALUE(Match) Match FOR rn IN (%s))
""", (
  SELECT STRING_AGG('' || n, ',') 
    FROM UNNEST(GENERATE_ARRAY(
                  1,
                  (SELECT COUNT(1) FROM sample_table 
                    GROUP BY Cid ORDER BY 1 DESC LIMIT 1)
               )) n
  ));

Query Result

Transform a Bigquery Table with variable columns

1 Answers1