All Forums Database
06 Jun 2014
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
 
 
 
 
 

Santanu84 122 posts Joined 04/13
08 Jun 2014

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

Raja_KT 1246 posts Joined 07/09
09 Jun 2014

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;

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.