CODEWITHSUNDEEP
monetdb
Arketes
Arketes

Reputation: 15

MonetDB: How to flatten comma separated column into individual rows

I have a column which has comma separated values.

--------
items
--------
a, b
--------

I want to list them as separate rows like:

------
Items
_____
a
b
----

I can't seem to find the syntax to do this.

For other datbases I see that we can use unnest, but I can't seem to get it to work for monetDB, though they have unnest listed as a keyword they do not have any documentation (or I can't find) on how to use it.

I have tried

      select unnest(items) from table

but I get foloowing: no such unary operator 'unnest'(clob)

I think unnest needs an array but I couldn't find any documentation to convert clob to array.

Any help is appreaciated

Upvotes: 0

Views: 46

Answers (1)

Jennie
Jennie

Reputation: 405

MonetDB doesn't have such unnest function out-of-the-box, but with the available string functions, I can produce query results similar to what you described:

create table t (id int auto_increment, s string);
insert into t(s) values ('hello,world'), ('flatten,comma');

-- if the order doesn't matter:
select left(s, charindex(',', s)-1) from t union all select right(s, length(s)-charindex(',', s)) from t;
+---------+
| %1      |
+=========+
| hello   |
| flatten |
| world   |
| comma   |
+---------+

-- if the order matters:
select unnested from (select id, left(s, charindex(',', s)-1) as unnested from t union all select id, right(s, length(s)-charindex(',', s)) as unnested from t) tmp order by id;
+----------+
| unnested |
+==========+
| hello    |
| world    |
| flatten  |
| comma    |
+----------+

Is this what you're looking for? There are more string functions you can use to produce such results [1], I just picked the ones that first came into mind.

[1] https://www.monetdb.org/documentation-Dec2023/user-guide/sql-functions/string-functions/

Upvotes: 0

Related Questions