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.

CodePudding user response：

If the input is provided as ('Bob','Alice') then

CodePudding user response：

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;

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;