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))