CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformsnowflake-schema
Klaidi Ziaj
Klaidi Ziaj

Reputation: 185

SNOWFLAKE SQL turn column values into row based off of ID

Hi I am using snowflake and have a player table enter image description here

I have the ID and Player Columns in the table and I am trying to list all of the players in one row based off of the ID value.

So for example ID 1610612748 will only have one row and all of the players listed in the player column on that row delimited by a comma.

Upvotes: 0

Views: 410

Answers (1)

Simon D
Simon D

Reputation: 6229

I think you are looking for the LIST_AGG aggregate function. Something like this should work:

Create example table:

create or replace transient table test_table(id number, player number) as
select
    column1 as id,
    column2 as player
from
values
       (1610612748, 123),
       (1610612748, 231),
       (1610612748, 333),
       (1610612748, 321),
       (1111111111, 123),
       (1111111111, 421),
       (2222222222, 123)
;

Run Query

select
    id,
    listagg(player ,',')
from test_table
group by id

Results

+----------+--------------------+
|ID        |LISTAGG(PLAYER ,',')|
+----------+--------------------+
|1111111111|123,421             |
|2222222222|123                 |
|1610612748|123,231,333,321     |
+----------+--------------------+

Upvotes: 2

Related Questions