从大查询行到列

Question

我有一个类似的表格

记分表

>td style="text align:central；">400>td style="text align:central；">650

id
12345	ABC
12345	PQR
12345	XYZ	350

考虑以下使用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));

查询结果

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