Suppose I have the following table. How to pivot three columns prefixed with part_ per customer_key for the top 3 orders?
+--------------+----------+---------------+-------------+----------------+
| CUSTOMER_KEY | PART_KEY | PART_QUANTITY | PART_AMOUNT | TOP_ORDER_RANK |
+--------------+----------+---------------+-------------+----------------+
| 10003 | 98909 | 39 | 74,408.10 | 1 |
| 10003 | 157096 | 49 | 56,501.41 | 2 |
| 10003 | 179085 | 42 | 48,891.36 | 3 |
| 10003 | 179075 | 10 | 28,891.36 | 4 |
+--------------+----------+---------------+-------------+----------------+
Expected result:
+--------------+------------+-----------------+---------------+------------+-----------------+---------------+------------+-----------------+---------------+
| CUSTOMER_KEY | PART_1_KEY | PART_1_QUANTITY | PART_1_AMOUNT | PART_2_KEY | PART_2_QUANTITY | PART_2_AMOUNT | PART_3_KEY | PART_3_QUANTITY | PART_3_AMOUNT |
+--------------+------------+-----------------+---------------+------------+-----------------+---------------+------------+-----------------+---------------+
| 10003 | 98909 | 39 | 74,408.10 | 157096 | 49 | 56,501.41 | 179085 | 42 | 48,891.36 |
+--------------+------------+-----------------+---------------+------------+-----------------+---------------+------------+-----------------+---------------+
Example data:
WITH t1 AS (
SELECT '10003' AS CUSTOMER_KEY, '98909' AS PART_KEY, 39 AS PART_QUANTITY, 74408.10 AMOUNT, 1 TOP_ORDER_RANK UNION ALL
SELECT '10003' AS CUSTOMER_KEY, '157096' AS PART_KEY, 49 AS PART_QUANTITY, 56501.41 AMOUNT, 2 TOP_ORDER_RANK UNION ALL
SELECT '10003' AS CUSTOMER_KEY, '179085' AS PART_KEY, 42 AS PART_QUANTITY, 48891.36 AMOUNT, 3 TOP_ORDER_RANK UNION ALL
SELECT '10003' AS CUSTOMER_KEY, '179075' AS PART_KEY, 10 AS PART_QUANTITY, 28891.36 AMOUNT, 4 TOP_ORDER_RANK
)
You can pivot with conditional aggregation. This is standard SQL that does not rely on a vendor-specific implementation such as Snowflake’s pivot:
select CUSTOMER_KEY,
max(case when TOP_ORDER_RANK = 1 then PART_KEY end) PART_KEY_1,
max(case when TOP_ORDER_RANK = 1 then PART_QUANTITY end) PART_QUANTITY_1,
max(case when TOP_ORDER_RANK = 1 then PART_AMOUNT end) PART_AMOUNT_1,
max(case when TOP_ORDER_RANK = 2 then PART_KEY end) PART_KEY_2,
max(case when TOP_ORDER_RANK = 2 then PART_QUANTITY end) PART_QUANTITY_2,
max(case when TOP_ORDER_RANK = 2 then PART_AMOUNT end) PART_AMOUNT_2,
max(case when TOP_ORDER_RANK = 3 then PART_KEY end) PART_KEY_3,
max(case when TOP_ORDER_RANK = 3 then PART_QUANTITY end) PART_QUANTITY_3,
max(case when TOP_ORDER_RANK = 3 then PART_AMOUNT end) PART_AMOUNT_3
from t1
group by CUSTOMER_KEY