# Aggregate values from different columns into an array

**URL:** <https://community.dremio.com/t/aggregate-values-from-different-columns-into-an-array/12641>\
**Category:** Uncategorized\
**Created:** [December 24, 2024, 2:30pm UTC](https://community.dremio.com/t/aggregate-values-from-different-columns-into-an-array/12641 "2024-12-24T14:30:50Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![melaanya](https://avatars.discourse-cdn.com/v4/letter/m/2acd7d/32.png) [@melaanya](https://community.dremio.com/u/melaanya)\
**Post date:** [December 24, 2024, 2:30pm UTC](https://community.dremio.com/t/aggregate-values-from-different-columns-into-an-array/12641/1 "2024-12-24T14:30:50Z")

</div>

Hello!

I have the following need: given 3 float columns, create a column with an array containing these 3 floats.

So I made the following attempts (creating a minimal example here):

```auto
-- Create a table with H, S, and V columns
CREATE TABLE test_hsv_values AS
SELECT 
    CAST(RANDOM() AS FLOAT) AS h, -- Random float between 0 and 1 for hue
    CAST(RANDOM() AS FLOAT) AS s, -- Random float between 0 and 1 for saturation
    CAST(RANDOM() AS FLOAT) AS v -- Random float between 0 and 1 for value
FROM (SELECT * FROM (VALUES(1),(2),(3),(4),(5))) AS t(dummy_column);

-- this does not work -- is there a better way?
select array[h, s, v] from test_hsv_values;

-- preparation step for the next approach
create table test_hsv_strings as (
select cast(concat('[', h, ',', s, ',', v, ']') as varchar) as color_hsv_string from "dremio-poc-jean"."dev-anna".test_hsv_values
);

-- this does not work with an error: AssertionError: Cannot add expression of different type to set
create table test_hsv as (
    select cast(convert_from(color_hsv_string, 'json') as list<float>) as color_hsv from "dremio-poc-jean"."dev-anna".test_hsv_strings
)

```

So I have a couple questions with this regard:

1. Is there a more optimal way to make an array with values of several columns than through a to-and-back string convertation? Does the type of values matter – floats, integers, etc.?
2. If not, and the only possible way is through this `convert_to` approach – how can I bypass the fact that even explicit type convertation is not enough to create a table? Pre-creation of a table is not an option, since in fact I’m using dbt, and this is just a minimal example of what is happening

In general, I would be glad to get any advice on how to achieve what I initially wanted – create an array with values of several columns (either all floats, or all integers).

Thank you!

---

<div class="post-metadata">

**Author:** ![Benny\_Chow](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/benny_chow/32/5527_2.png) [@Benny\_Chow](https://community.dremio.com/u/Benny_Chow)\
**Post date:** [January 5, 2025, 7:05pm UTC](https://community.dremio.com/t/aggregate-values-from-different-columns-into-an-array/12641/2 "2025-01-05T19:05:44Z")

</div>

Hi, the second SQL should have worked. I’ve asked the query planning team to take a look. I would try to avoid using CONVERT\_FROM for this use case of constructing an array from a bunch of columns.

---

<div class="post-metadata">

**Author:** ![melaanya](https://avatars.discourse-cdn.com/v4/letter/m/2acd7d/32.png) [@melaanya](https://community.dremio.com/u/melaanya)\
**Post date:** [January 6, 2025, 4:49pm UTC](https://community.dremio.com/t/aggregate-values-from-different-columns-into-an-array/12641/3 "2025-01-06T16:49:15Z")

</div>

thank you for your response! what approach would you advise to use in this particular case when I want to construct an array from a fixed small number of columns?