Concatenate value of multiple rows into one Single row (Distinct Values)

Database

Concatenate value of multiple rows into one Single row (Distinct Values)

Hello

 

I am following up this forum http://community.teradata.com/t5/Database/concatenate-value-of-multiple-rows-into-one-Single-row/m-p...

 

I used following Query which was given by Dieter in above mentioned forum. Thanks Dieter for your help. Everything works fine in the below query, but my problem here is I do not want a repeated values in a field. Example, if “new York” value is repeated more times for same parent Id then I just want to one New York value in Location field rather New York, New York , New York.

 

CREATE VOLATILE TABLE vt_temp AS (
 SELECT
   Parent_ID                    
   ,Child_ID                     
   ,city_nm                      
   ,EMAIL_ADDR                   
   ,ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY child_id) AS rn
FROM temp
) WITH DATA PRIMARY INDEX(parent_id) ON COMMIT PRESERVE ROWS;


WITH RECURSIVE rec_test(parent,child, location,mail,LVL)
   AS
   (
    SELECT parent_id,child_id (VARCHAR(1000)),city_nm (VARCHAR(1000)),email_addr, 1
    FROM vt_temp
    WHERE rn = 1
    UNION ALL
    SELECT  parent_id, TRIM(child_id) || ', ' || child, TRIM(city_nm)  || ', ' || location ,email_addr,LVL+1
    FROM vt_temp INNER JOIN rec_test
    ON parent_id = parent
   AND vt_temp.rn = rec_test.lvl+1
   )
   SELECT parent,child, location,mail,LVL
   FROM rec_test
QUALIFY RANK() OVER(PARTITION BY parent ORDER BY LVL DESC) = 1;

 

My Base Data Set

Parent_ID

Child_ID

Location

Parent_email_addr

1

sales

Portland

gb

1

finance

New york

gb

1

cc

New york

gb

2

Risk Mangement

New Orleans

BC

2

Health care

New orleans

BC

3

finance

salem

BD

3

CC

LA

BD

4

SALES

NY

DB

 

After Using above mentione Recursive Query, My data set is like below

parent

child

location

mail

LVL

1

sales, finance, cc

Portland, New york, New york

gb

3

2

Risk Mangement, Health care

New Orleans, New orleans

BC

2

3

finance, CC

salem, LA

BD

2

4

SALES

NY

DB

1

 

What I need is

I want distinct values in a location field. Like below result.

parent

child

location

mail

LVL

1

sales, finance, cc

Portland, New york

gb

3

2

Risk Mangement, Health care

New Orleans

BC

2

3

finance, CC

salem, LA

BD

2

4

SALES

NY

DB

1

 

It would be really helpful, if someone can tell me, How to get a distinct values in a cell.

 

Thanking you in advance.

Deva

Tags (2)

Accepted Solutions
Junior Contributor

Re: Concatenate value of multiple rows into one Single row (Distinct Values)

You can add a condition to check if the location is already in the concatenated string and skip it:

 

WITH RECURSIVE rec_test(Parent,child, location,mail,LVL)
   AS
   (
    SELECT parent_id,child_id (VARCHAR(1000)),city_nm (VARCHAR(1000)),email_addr, 1
    FROM vt_temp
    WHERE rn = 1
    UNION ALL
    SELECT  parent_id, Trim(child_id)  ||  ', ' || child  , 
       CASE WHEN ', ' || location || ',' LIKE '%, ' || city_nm || ',%' THEN '' ELSE Trim(city_nm)  || ', ' end || location,
       email_addr,LVL+1
    FROM vt_temp INNER JOIN rec_test
    ON parent_id = Parent
   AND vt_temp.rn = rec_test.lvl+1
   )
   SELECT Parent,child, location,mail,LVL
   FROM rec_test
QUALIFY Rank() Over(PARTITION BY Parent ORDER BY LVL DESC) = 1;
1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: Concatenate value of multiple rows into one Single row (Distinct Values)

You can add a condition to check if the location is already in the concatenated string and skip it:

 

WITH RECURSIVE rec_test(Parent,child, location,mail,LVL)
   AS
   (
    SELECT parent_id,child_id (VARCHAR(1000)),city_nm (VARCHAR(1000)),email_addr, 1
    FROM vt_temp
    WHERE rn = 1
    UNION ALL
    SELECT  parent_id, Trim(child_id)  ||  ', ' || child  , 
       CASE WHEN ', ' || location || ',' LIKE '%, ' || city_nm || ',%' THEN '' ELSE Trim(city_nm)  || ', ' end || location,
       email_addr,LVL+1
    FROM vt_temp INNER JOIN rec_test
    ON parent_id = Parent
   AND vt_temp.rn = rec_test.lvl+1
   )
   SELECT Parent,child, location,mail,LVL
   FROM rec_test
QUALIFY Rank() Over(PARTITION BY Parent ORDER BY LVL DESC) = 1;

Re: Concatenate value of multiple rows into one Single row (Distinct Values)

Thank you so much Dieter, You are Great!