All Forums Database
akyz 5 posts Joined 05/14
03 Jun 2014
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#comment- 133576
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!
 

-AK
Santanu84 122 posts Joined 04/13
06 Jun 2014

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
 

You must sign in to leave a comment.