Bigquery rows to columns

AlpsToronto 0
Viewed 56

I have a table like below

scores_table

id item score
12345 ABC 400
12345 PQR 650
12345 XYZ 350

I want to convert it to a single row and distribute the values across multiple columns.

id type1 score1 type2 score2 type3 score3
12345 ABC 400 XYZ 350 PQR 650

There are only 3 types and scores for every id. How do I achieve this ? Thank you in advance.

pivot transpose google-cloud-platform google-bigquery sql
1 Answers

Consider below query using PIVOT,

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY id) rn
    FROM sample_table
) PIVOT (ANY_VALUE(item) type, ANY_VALUE(score) score FOR rn IN (1, 2, 3));

Query results

+-------+--------+---------+--------+---------+--------+---------+
|  id   | type_1 | score_1 | type_2 | score_2 | type_3 | score_3 |
+-------+--------+---------+--------+---------+--------+---------+
| 12345 | XYZ    |     350 | PQR    |     650 | ABC    |     400 |
+-------+--------+---------+--------+---------+--------+---------+
Jaytiger0
Related