COALESCE Row with Group By & SUM Charges

Teradata Studio
Fan

COALESCE Row with Group By & SUM Charges

COALESCE Row with Group By & SUM Charges

I have following data:

TBL1

 

RPT_MONTH

ACCOUNT_TYPE

CUST_ID

CT_Type

Charges

2016-08

CONSUMER

15220

TV

 $   90.00

2016-08

CONSUMER

15220

Phone

 $   65.00

2016-08

CONSUMER

15230

TV

 $ 120.00

2016-08

CONSUMER

15230

Phone

 $   75.00

2016-08

CONSUMER

15240

TV

 $ 150.00

2016-08

CONSUMER

15250

Phone

 $   90.00

 

Need Result:

 

RPT_MONTH

ACCOUNT_TYPE

CUST_ID

CT_Type

Charges

2016-08

CONSUMER

15220

TV, Phone

 $ 155.00

2016-08

CONSUMER

15230

TV, Phone

 $ 195.00

2016-08

CONSUMER

15240

TV

 $ 150.00

2016-08

CONSUMER

15250

Phone

 $   90.00

 

I have tried following but getting error:

User does not have Execute Function Access to TDStats.udfConcat

SELECT DISTINCT

RPT_MONTH,

ACCOUNT_TYPE,

CUST_ID,

tdstats.udfconcat(TRIM(CT_Type)) as CT_Type,

SUM(Charges) AS Charges,

FROM TBL1

GROUP BY 1,2,3;

 

Thanks,

BipP


Accepted Solutions
Junior Contributor

Re: COALESCE Row with Group By & SUM Charges

For a small list of known values better use conditional aggregation:

   Trim(Trailing ',' FROM Max(CASE WHEN CT_Type = 'TV'    THEN 'TV,'   ELSE '' END) ||
                          Max(CASE WHEN CT_Type = 'Phone' THEN 'Phone' ELSE '' END))

 

1 ACCEPTED SOLUTION
3 REPLIES
Junior Contributor

Re: COALESCE Row with Group By & SUM Charges

How many CT_Types exist (per customer)?

Is it known, small number?

Fan

Re: COALESCE Row with Group By & SUM Charges

Only two. TV and Phone.

Junior Contributor

Re: COALESCE Row with Group By & SUM Charges

For a small list of known values better use conditional aggregation:

   Trim(Trailing ',' FROM Max(CASE WHEN CT_Type = 'TV'    THEN 'TV,'   ELSE '' END) ||
                          Max(CASE WHEN CT_Type = 'Phone' THEN 'Phone' ELSE '' END))