Convert a column into distinct comma separated list in teradata

General

Convert a column into distinct comma separated list in teradata

Hi All,

I am tring to convert column into distinct comma separated list using XMLAGG function, however i am not getting required output. Could you please help me how to give distinct clause in the below syntax.

Query using : PRIMARY_NAME,TRIM(TRAILING ',' FROM (XMLAGG(TRIM( BR_CD)|| ',' ORDER BY PRIMARY_NAME) (VARCHAR(10000)))) CLASSIFICATION

 PRIMARY_NAME                                                                                                    CLASSIFICATION

  ----------------------------        ------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    CMPNY-A                            RESTORATION TIME, ADVANCE REPLACEMENT, RESTORATION TIME, RESPONSE TIME, RESTORATION TIME, RESTORATION TIME

    CMPNY-B                            ADVANCE REPLACEMENT, RESTORATION TIME, RESPONSE TIME, RESTORATION TIME

Req O/P ;

 PRIMARY_NAME                                                                                                    CLASSIFICATION

  ----------------------------        ------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    CMPNY-A                            RESTORATION TIME, ADVANCE REPLACEMENT, RESPONSE TIME

    CMPNY-B                            ADVANCE REPLACEMENT, RESTORATION TIME, RESPONSE TIME

Regards,

Pavan

1 REPLY
Teradata Employee

Re: Convert a column into distinct comma separated list in teradata

You need to use a nested query with GROUP BY PRIMARY_NAME, BR_CD to return only distinct combinations, then use XMLAGG in the outer query to concatenate into one row.