Search code examples
sqlkognitio-wx2kognitiowx2

concatenate and de-dupe multiple rows

I have some incoming rows in the below format.

| Col1 | Col2 | Col3 |
| 1    | A    | 1    |
| 1    | A    | 1,2  |
| 1    | A    | 1,3  |
| 1    | A    | 2,4  |

Desired outputsql is 

| Col1 | Col2 | Col3    |
| 1    | A    | 1,2,3,4 |

Basically, group all rows based on Col1 and Col2 and then concatenate and remove duplicates from Col3.

SELECT COL1, COL2, {?????}
FROM TABLEA
GROUP BY COL1, COL2;

I could not think much at this moment. Any pointers would be much appreciated. I am inclined to WX2 database, but any ANSI compliant snippet would be helpful.

Solution

  • For Postgres use this:

    select col1, col2, string_agg(distinct col3, ',') as col3
from (
   select col1, col2, x.col3
   from tablea, unnest(string_to_array(col3, ',')) as x(col3)
) t
group by col1, col2;

    This is largely ANSI compliant except for the string_to_array() and string_agg() function.