Concatenate values from one field in multiple rows into one one field on all rows

Database
Enthusiast

Concatenate values from one field in multiple rows into one one field on all rows

Hello,

This question is a spinoff of the following thread:

http://forums.teradata.com/forum/database/concatenate-value-of-multiple-rows-into-one-single-row-1#c...

I have been utilizing the recursive query code provided by Dieter in the link above (works great), but I have a slight variation on the original question.  How would Dieter's code change if we wanted to concatenate all child_ids todether into one field for a given parent_id, however, we want to keep separate records for each Location?

Using the same source data:

Parent ID   Child ID                     Location                    Parent_EMAIL_ADDR

1                 Sales                          Portland                     1ABC@XYZ.COM

1                 Finance                      San Francisco             1ABC@XYZ.COM

1                 CC                              New York                   1ABC@XYZ.COM

2                 Risk Mgmt                   New Orleans              2DEF@XYZ.COM

2                 Healthcare                 Chicago                      2DEF@XYZ.COM

3                 Finance                      Salem                         3GHI@XYZ.COM

3                 CC                              Los Angeles               3GHI@XYZ.COM

4                 Sales                          Houston                     4JKL@XYZ.COM

This time, we would want this expected output:

Parent ID   Child ID                         Location                    Parent_EMAIL_ADDR

1                 CC, Finance, Sales         Portland                     1ABC@XYZ.COM

1                 CC, Finance, Sales         San Francisco             1ABC@XYZ.COM

1                 CC, Finance, Sales         New York                   1ABC@XYZ.COM

2                 Healthcare, Risk Mgmt   New Orleans              2DEF@XYZ.COM

2                 Healthcare, Risk Mgmt   Chicago                      2DEF@XYZ.COM

3                 CC, Finance                    Salem                         3GHI@XYZ.COM

3                 CC, Finance                    Los Angeles               3GHI@XYZ.COM

4                 Sales                              Houston                     4JKL@XYZ.COM

Thanks in advance!

1 REPLY
Enthusiast

Re: Concatenate values from one field in multiple rows into one one field on all rows

Hi

This is how, I would do it. Hope this will be helpful. Someone may have a better option.

CREATE MULTISET TABLE TABLE2

(

PARENTID INTEGER,

CHILDID VARCHAR(20),

LOCATION VARCHAR(20),

EMAIL VARCHAR(20)

)

NO PRIMARY INDEX

;

INSERT INTO TABLE2 VALUES (1, 'SALES', 'PORTLAND', '1ABC@XYZ.COM') ;

INSERT INTO TABLE2 VALUES (1, 'FINANCE', 'SAN FRANCISCO', '1ABC@XYZ.COM') ;

INSERT INTO TABLE2 VALUES (1, 'CC', 'NEW YORK', '1ABC@XYZ.COM') ;

INSERT INTO TABLE2 VALUES (2, 'RISK MGMT', 'NEW ORLEANS', '2DEF@XYZ.COM') ;

INSERT INTO TABLE2 VALUES (2, 'HEALTHCARE', 'CHICAGO', '2DEF@XYZ.COM') ;

INSERT INTO TABLE2 VALUES (4, 'SALES', 'HOUSTON', '4JKL@XYZ.COM') ;

CREATE VOLATILE TABLE TABLE3 AS

(SELECT PARENTID, CHILDID, ROW_NUMBER() OVER(PARTITION BY PARENTID ORDER BY PARENTID, CHILDID) AS RCNT FROM TABLE2) WITH DATA

ON COMMIT PRESERVE ROWS

;

WITH RECURSIVE SPIN_DATA(PARENTID, CHILDID, RCNT)

AS

(

SELECT PARENTID, CHILDID, RCNT

FROM TABLE3

WHERE RCNT = 1

UNION ALL

SELECT DIRECT.PARENTID, DIRECT.CHILDID||','||INDIRECT.CHILDID, INDIRECT.RCNT AS RCNT

FROM TABLE3 INDIRECT, SPIN_DATA AS DIRECT

WHERE INDIRECT.PARENTID = DIRECT.PARENTID

AND INDIRECT.RCNT = DIRECT.RCNT + 1

)

SELECT T.PARENTID, S.CHILDID, T.LOCATION, T.EMAIL FROM SPIN_DATA S

INNER JOIN

TABLE2 T

ON S.PARENTID = T.PARENTID

QUALIFY ROW_NUMBER() OVER(PARTITION BY T.PARENTID,T.LOCATION ORDER BY S.RCNT DESC) = 1

ORDER BY 1, 3

;

Thanks 

Santanu