# How to unnest multiple columns in Trino, outputting into corresponding rows

**URL:** <https://www.starburst.io/community/forum/t/how-to-unnest-multiple-columns-in-trino-outputting-into-corresponding-rows/47>\
**Category:** SQL\
**Created:** [October 12, 2021, 8:31pm UTC](https://www.starburst.io/community/forum/t/how-to-unnest-multiple-columns-in-trino-outputting-into-corresponding-rows/47 "2021-10-12T20:31:45Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![system](https://www.starburst.io/community/forum/user_avatar/www.starburst.io/system/32/1_2.png) [@system](https://www.starburst.io/community/forum/u/system)\
**Post date:** [October 12, 2021, 8:31pm UTC](https://www.starburst.io/community/forum/t/how-to-unnest-multiple-columns-in-trino-outputting-into-corresponding-rows/47/1 "2021-10-12T20:31:45Z")

</div>

Originally posted on [Stack Overflow](https://stackoverflow.com/questions/55815389)

I’m trying to unnest some code

I have a a couple of columns that have arrays, both columns using | as a deliminator

[The data would be stored looking like this, with extra values to the side which show the current currency](https://i.stack.imgur.com/ujmw5.png)

[I want to output it like this](https://i.stack.imgur.com/Byitb.png) I tried doing another unnest column, like this

```auto
SELECT c.campaign, c.country, a.product_name, u.price -- add price to this split. handy for QBR

FROM c, UNNEST(split(price, '|')) u(price), UNNEST(split(product_name, '|')) a(product_name)

group by 1,2, 3, 4

```

but this duplicated several rows, so I’m not sure if unnesting the two columns doesn’t quite work

---

<div class="post-metadata">

**Author:** ![martin](https://www.starburst.io/community/forum/user_avatar/www.starburst.io/martin/32/24_2.png) [@martin](https://www.starburst.io/community/forum/u/martin)\
**Post date:** [October 12, 2021, 10:12pm UTC](https://www.starburst.io/community/forum/t/how-to-unnest-multiple-columns-in-trino-outputting-into-corresponding-rows/47/2 "2021-10-12T22:12:41Z")

</div>

The issue with your query is that the clause `FROM c, UNNEST(...), UNNEST(...)` is effectively computing the cross join between each row of `c` and the rows produced by each of the derived tables resulting from the `UNNEST` calls.

You can solve it by unnesting all your arrays in a single call to [`UNNEST`](https://trino.io/docs/current/sql/select.html#unnest), thus, producing a single derived table. When used in that manner, the `UNNEST` produces a table with one column for each array and one row for each element in the arrays. If the arrays have a different length, it will produce rows up to the number of elements in the largest array and fill in with `NULL` for the column of the smaller array.

To illustrate, for your case, this is what you want:

```auto
WITH data(a, b, c) AS (
    VALUES
        ('a|b|c', '1|2|3', 'CAD'),
        ('d|e|f', '4|5|6', 'USD')
)
SELECT t.a, t.b, data.c
FROM data, UNNEST(split(a, '|'), split(b, '|')) t(a, b)

```

which produces:

```auto
 a | b | c
---+---+-----
 a | 1 | CAD
 b | 2 | CAD
 c | 3 | CAD
 d | 4 | USD
 e | 5 | USD
 f | 6 | USD
(6 rows)

```