# Issue

This Content is from Stack Overflow. Question asked by Joshua Smart-Olufemi

For example I have two columns:

``````Column A: dog, cat, mouse

Column B: truck, jeep, lorry
``````

I want a situation where:

``````Column C : dog, truck, cat, jeep, mouse, lorry
``````

I am using Snowflake

# Solution

Assuming that columns colA, colB are strings, the values should be first splitted to atomic values SPLIT_TO_TABLE and combined again LISTAGG:

``````SELECT ID, COLA, COLB, LISTAGG(COL, ', ') AS colC
FROM (
SELECT ID, COLA, COLB, TRIM(s1.VALUE::STRING) AS col
FROM tab
,TABLE(SPLIT_TO_TABLE(tab.colA, ',')) AS s1
UNION
SELECT ID, COLA, COLB, TRIM(s2.VALUE::STRING) AS col
FROM tab
,TABLE(SPLIT_TO_TABLE(tab.colB, ',')) AS s2
) AS sub
GROUP BY ID, COLA, COLB
ORDER BY ID;
``````

For sample data:

``````CREATE OR REPLACE TABLE tab
AS
SELECT 1 AS id, 'dog, cat, mouse' AS colA, 'truck, jeep, lorry' AS colB UNION
SELECT 2 AS id, 'sparrow' AS colA, 'sparrow, parrot' AS colB;
``````

Output:

Sidenote: For storing non-atomic values ARRAY is a better choice:

``````CREATE OR REPLACE TABLE tab
AS
SELECT 1 AS id, ['dog', 'cat', 'mouse'] AS colA, ['truck', 'jeep', 'lorry'] AS colB UNION
SELECT 2 AS id, ['sparrow'] AS colA, ['sparrow', 'parrot'] AS colB;
``````

Then combining is a matter of using ARRAY_UNION_AGG:

``````SELECT ID, ARRAY_UNION_AGG(COL) AS COLC
FROM (
SELECT ID, COLA AS col FROM tab
UNION ALL
SELECT ID, COLB AS col FROM tab
) sub
GROUP BY ID
ORDER BY ID;
``````

Output:

``` This Question was asked in  StackOverflow by  Joshua Smart-Olufemi and Answered by Lukasz Szozda It is licensed under the terms of
CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.```