SQL Snowflake - Put an SQL list / array into a column

Question

Based on a specific project architecture, I have a LIST ('Bob', 'Alice') that I want to SELECT as a column (and do a specific JOIN afterwards).

Right now, I did :

SELECT *
FROM TABLE(flatten(input => ('Bob', 'Alice'))) as v1

But this resulted in one row / two columns, and I need one column / two rows (to do the JOIN).

Same if I use :

select * from (values ('Bob', 'Alice'))

The basic idea would be to PIVOT, however, the list may be of arbitrary length so I can't manually list all column names in PIVOT query...

Also I can't use the following (which would work) :

select * from (values ('Bob'), ('Alice'))

because I inherit the list as a string and can't modify it on the fly.

Have you looked at SPLIT_TO_TABLE, would that work?

Maja F.
– Maja F.

2022-02-23 14:53:01 +00:00
Commented Feb 23, 2022 at 14:53 — Maja F.
– Maja F., Commented Feb 23, 2022 at 14:53

Simeon Pilgrim · Accepted Answer · 2022-02-23 21:58:45Z

3

If you have a fixed set of values that you are wanting to JOIN against, and looking at some of the SQL you have tried the correct form to use VALUES is:

select * from (values ('Bob'), ('Alice'));

or

select * from values ('Bob'), ('Alice');

if you have a exist array you can FLATTEN it like for first example

SELECT v1.value::text
FROM TABLE(flatten(input => array_construct('Bob', 'Alice'))) as v1;

V1.VALUE::TEXT
Bob
Alice

or if you have a string "Bob, Alice" then SPLIT_TO_TABLE

SELECT trim(v1.value)
FROM TABLE(split_to_table('Bob, Alice', ',')) as v1;

answered Feb 23, 2022 at 21:58

Simeon Pilgrim

26.7k3 gold badges38 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lukasz Szozda · Accepted Answer · 2022-02-23 17:11:16Z

0

If the input is provided as ('Bob','Alice') then STRTOK_SPLIT_TO_TABLE could be used:

SELECT table1.value 
FROM table(strtok_split_to_table($$('Bob','Alice')$$, '(),''')) AS table1;

Output:

answered Feb 23, 2022 at 17:11

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

SQL Snowflake - Put an SQL list / array into a column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related