Is it possible to SELECT into list?

[Admolly]

Assume I have a table like the following:

	col
0	a
1	b
2	c
3	c

I would like to do something like the following:

SELECT 'col' as column, list_value(SELECT col FROM table) as values

	column	values
0	col	['a', 'b', 'c', 'c']

or even

SELECT 'col' as column, list_value(SELECT DISTINCT col FROM table) as values

	column	values
0	col	['a', 'b', 'c']

Is this currently possible in duckdb?

[lnkuiper]

I believe you are looking for the list aggregate function:

D create table tbl (col varchar);
D insert into tbl values ('a'), ('b'), ('c'), ('c');
D select 'col' as column, list(col) as values from tbl;
┌────────┬──────────────┐
│ column │    values    │
├────────┼──────────────┤
│ col    │ [a, b, c, c] │
└────────┴──────────────┘
D select 'col' as column, list(distinct col) as values from tbl;
┌────────┬───────────┐
│ column │  values   │
├────────┼───────────┤
│ col    │ [a, b, c] │
└────────┴───────────┘

[Admolly]

Yes, perfect @lnkuiper! That is exactly what I was looking for.