Concatenate AND aggregate row values to columns

Database

Concatenate AND aggregate row values to columns

Hi,

I have a sample dataset like this

customer_id    product_code    product_price

1                      ELC1                  103

1                      HMO3                 200

1                      STD1                   10

2                      ELC2                  110

2                      HMO2                 150

2                      STD1                   10

Desired output would be like this:

customer_id    product_bundle    bundle_price

1                     ELC1,HMO3,STD1       213

2                     ELC2,HMO2,STD1       270

I have tried various partitioning and recursive functions, but with no success. TD seems to get particularly upset by a mixture of concatenation and aggregated values

Any pointers, suggestions, pseudocode welcome.

Thanks

2 REPLIES
Enthusiast

Re: Concatenate AND aggregate row values to columns

Hi

This may be helpful.

CREATE MULTISET TABLE TABLE2

(

CUSTOMER_ID INTEGER,

PRODUCT_CODE VARCHAR(20),

PRODUCT_PRICE DECIMAL(5,0)

)

NO PRIMARY INDEX

;

INSERT INTO TABLE2 VALUES(1, 'ELC1', 103) ;

INSERT INTO TABLE2 VALUES(1, 'HMO3', 200) ;

INSERT INTO TABLE2 VALUES(1, 'STD1', 10) ;

INSERT INTO TABLE2 VALUES(2, 'ELC2', 110) ;

INSERT INTO TABLE2 VALUES(2, 'HMO2', 150) ;

INSERT INTO TABLE2 VALUES(2, 'STD1', 10) ;

CREATE MULTISET VOLATILE TABLE TABLE2_VT AS

(SEL CUSTOMER_ID, PRODUCT_CODE, ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY PRODUCT_CODE) AS ORDR FROM TABLE2) WITH DATA

ON COMMIT PRESERVE ROWS

;

WITH RECURSIVE CON_AGG(CUSTOMER_ID, PRODUCT_CODE, ORDR)

AS

(

SEL CUSTOMER_ID, PRODUCT_CODE, ORDR

FROM TABLE2_VT

WHERE ORDR = 1

UNION ALL

SEL DIRECT.CUSTOMER_ID, DIRECT.PRODUCT_CODE||','||INDIRECT.PRODUCT_CODE AS PRODUCT_CODE, INDIRECT.ORDR

FROM TABLE2_VT INDIRECT, CON_AGG DIRECT

WHERE DIRECT.CUSTOMER_ID = INDIRECT.CUSTOMER_ID

AND INDIRECT.ORDR = DIRECT.ORDR + 1

)

SEL S.CUSTOMER_ID, S.PRODUCT_CODE, T.BUNDLE_PRICE FROM CON_AGG S

INNER JOIN

(SEL CUSTOMER_ID, SUM(PRODUCT_PRICE) AS BUNDLE_PRICE FROM TABLE2 GROUP BY 1) T

ON S.CUSTOMER_ID = T.CUSTOMER_ID

QUALIFY ROW_NUMBER() OVER(PARTITION BY S.CUSTOMER_ID ORDER BY S.ORDR DESC) = 1

;

Thanks

Santanu

Enthusiast

Re: Concatenate AND aggregate row values to columns

I m in 14. I work out this way:

select customer_id, sum(product_price),tdstats.udfconcat(trim(product_code)) from db1.table1 group by 1;