Search code examples
sqlgroup-bypivotsnowflake-cloud-data-platformlistagg

Using LISTAGG in a pivoting task

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!

Solution

  • An alternative approach using the cool function ARRAY_AGG().

    enter image description here

    SELECT   
  TRANSACTION_ID
, TRANSACTION_TYPE
, ARRAY_TO_STRING("'CA'",',') CA
, ARRAY_TO_STRING("'MA'",',') MA
, ARRAY_TO_STRING("'NY'",',') NY
FROM   
    T1
PIVOT (ARRAY_AGG(T1.DATE_TIMESTAMP) FOR T1.STATE IN ('CA','MA','NY'));

    where T1 was lifted from Felipe's excellent answer.

    with t1 as (
select transaction_id, state, transaction_type, date_timestamp
from (
    select split(value, '|') x, trim(x[0])  transaction_id, trim(x[1]) state, trim(x[2]) transaction_type, x[3]::string::timestamp date_timestamp
    from table(split_to_table(
    $$      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$$, '\n'))
)
)