Using LISTAGG in a pivoting task

Question

I have a table (database.schema.table1) with the following data:

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

I want my result set to look like this:

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

I have tried the following query but it doesn't seem to work in Snowflake for some reason (and have tried some variations of what I tried googling online):

SELECT *
FROM database.schema.table1 t1
PIVOT(LISTAGG(t1.time, '|') FOR t1.state IN ('CA', 'MA', 'NY')) AS p;

Is there any way I can try to use a LISTAGG in this pivot?

Thanks in advance!

Answer 1

Try with an explicit pivot operation:

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