Merge a variable number of rows with one column, into a single row

Analytics
Enthusiast

Merge a variable number of rows with one column, into a single row

I am trying to merge a variable number of rows with one column into a single row.  I find examples all over the place ordering by an id or some other value, but what if you don't care what the order is.  You just the rows all flattened into one row regardless of order.

Basically I am trying to get the following

column1

Red

Blue

Green

into a row reading 'Red, Blue, Green' without caring about sort order.

The only thing I have been able to figure out is to order by the single column name even though I don't care.  I am just trying to simplify this as much as possible and possibly do it without a volatile or intermediate table.

Here is the code I have so far:

CREATE TABLE TD.TABLE2 AS (

  SELECT DISTINCT

    column1

    ,RANK () OVER (ORDER BY column1) AS rnk

  FROM TD.TABLE1

) WITH DATA;

WITH RECURSIVE base (com_rnk, com_list)

AS

(

  SELECT

    rnk AS com_rnk, 

    CAST(TRIM(column1) AS VARCHAR(5000)) AS com_list

  FROM TD.TABLE2 

  WHERE rnk = 1

  UNION ALL

  SELECT

    rnk AS com_rnk,

    b.com_list|| ',' || CAST(TRIM(c.column1) AS VARCHAR(5000)) AS com_list

  FROM TD.TABLE2 c

  INNER JOIN base b

  ON b.com_rnk + 1 = c.com_rnk

)

SELECT  com_list

FROM base

QUALIFY RANK() OVER (ORDER BY com_rnk DESC) = 1

;

3 REPLIES
Enthusiast

Re: Merge a variable number of rows with one column, into a single row

Which TD version? Have you seen this function, if it helps you,

select tdstats.udfconcat(trim(columns1)) from your_table;

Enthusiast

Re: Merge a variable number of rows with one column, into a single row

We are currently using 13.1 and no, I had not seen that UDF before.  I tried it out and it is not supported on our system.  I will check with our DBA team and see if I can have it added.

Enthusiast

Re: Merge a variable number of rows with one column, into a single row

Thanks Raja.. i found the UDF existing and  That worked like a charm....

regards

RK