sql - 如何在 Snowflake 的枢轴中使用 LISTAGG？

Question

有一个包含以下数据的表 (database.schema.table1):

transaction_id |  state | transaction_type | date_timestamp
        1      |   CA   |     Payment      |   12/1/2022 01:00:00
        1      |   CA   |     Payment      |   12/1/2022 02:00:00
        1      |   MA   |     Payment      |   12/1/2022 01:00:00
        2      |   MA   |     Refund       |   12/1/2022 01:00:00
        3      |   NY   |     Payment      |   12/1/2022 01:00:00
        4      |   MA   |     Payment      |   12/1/2022 03:00:00

我希望我的结果集看起来像这样:

   transaction_id | transaction_type | CA                                     | NY                 | MA
         1        |   Payment        | 12/1/2022 01:00:00, 12/1/2022 02:00:00 |                    | 12/1/2022 01:00:00
         2        |   Refund         |                                        | 12/1/2022 01:00:00 | 
         3        |   Payment        |                                        | 12/1/2022 01:00:00 |
         4        |   Payment        | 12/1/2022 03:00                        |                    |

我尝试了以下查询，但由于某种原因它似乎在 Snowflake 中不起作用(并且尝试了我尝试在线谷歌搜索的一些变体):

   select *
    from database.schema.table1 t1
   pivot (listagg(t1.time, '|') for t1.state in ('CA', 'MA', 'NY')) as p;

有什么方法可以尝试在此数据透视表中使用 listagg？提前致谢!

Answer 1

尝试使用显式枢轴操作:

SELECT transaction_id, 
       transaction_type,
       LISTAGG(CASE WHEN state = 'CA' THEN date_timestamp END, ', ') AS CA,
       LISTAGG(CASE WHEN state = 'NY' THEn date_timestamp END, ', ') AS NY,
       LISTAGG(CASE WHEN state = 'MA' THEn date_timestamp END, ', ') AS MA
FROM tab
GROUP BY transaction_id,
         transaction_type