concatenating historical records

[meter_man]

Is there a way to bring historical data into one field? I have activities
A1, A2, A3 ,etc in a history table. I need to check for the presence of
that data and bring it back if it exists for a record. I know I can do this
using (not tested):

With #temp as (

Select RecordID, Name from Table1 where . . . )

Select

RecordID

, Name

, substr((#A1.Activity || #A2.Activity || #A3.Activity),2) as History

from

#Temp T

left join (

Select ',' || Activity_CD as Activity from

#Temp T INNER JOIN History H on T.RecordID = H. RecordID

WHERE Activity_CD = 'A1') as #A1 on T.RecordID = #A1.RecordID

left join (

Select ',' || Activity_CD as Activity from

#Temp T INNER JOIN History H on T.RecordID = H. RecordID

WHERE Activity_CD = 'A2') as #A2 on T.RecordID = #A2.RecordID

left join (

Select ',' || Activity_CD as Activity from

#Temp T INNER JOIN History H on T.RecordID = H. RecordID

WHERE Activity_CD = 'A3') as #A3 on T.RecordID = #A3.RecordID

I don't have permissions to do a Stored Proc and I have to concatenate about
20 of these things. Is there an easier way so I can result in history only
reading like "A1, A3, A10" ?

TIA

candyman

[Knut Stolze]

meter_man wrote:

Have a look at recursive queries and "Bill of materials" in the manual.
Alternatively, you can use the XMLAGGREGATE function to do the aggregation
from multiple rows into a single one.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[jefftyzzer]

Here's an example applying a recursive CTE, using an algorithm from
Molinaro's _SQL Cookbook_ (note: best viewed with fixed font):

WITH
PARTICIPANT_NAME
(
PRTCP_NM_ID,
PRTCP_ID,
FIRST_NM
)
AS
(VALUES (1,1,'JEFF'),
(2,1,'JEFFREY'),
(3,2,'EDDIE'),
(4,2,'EDWARD'),
(5,2,'TED'),
(6,3,'ELIZABETH')
),
PRTCP_NM
(
PRTCP_ID,
CNT,
LIST,
PRTCP_NM_ID,
LEN
)
AS
(
SELECT
PN1.PRTCP_ID,
COUNT(*) OVER (PARTITION BY PN1.PRTCP_ID),
CAST(PN1.FIRST_NM AS VARCHAR(4000)),
PN1.PRTCP_NM_ID,
1
FROM
PARTICIPANT_NAME PN1
UNION ALL
SELECT
PRTCP_NM.PRTCP_ID,
PRTCP_NM.CNT,
PRTCP_NM.LIST||', '||PN2.FIRST_NM,
PN2.PRTCP_NM_ID,
PRTCP_NM.LEN + 1
FROM
PARTICIPANT_NAME PN2,
PRTCP_NM
WHERE
PN2.PRTCP_ID = PRTCP_NM.PRTCP_ID
AND
PN2.PRTCP_NM_ID > PRTCP_NM.PRTCP_NM_ID
)
SELECT
LIST
FROM
PRTCP_NM
WHERE
LEN = CNT

and here's a really cool example--from this forum, BTW--of using the
XMLAGG function:

CREATE TABLE EMPLOYEE(NAME VARCHAR(15), DEPT VARCHAR(15));

NAME DEPT
----- ------
MISO SOLUTIONS
JOHN DEVELOPMENT
SERGE SOLUTIONS
LEE L3
MARK ID
JACK L3
LILY QUALITY
BERNI SOLUTIONS

SELECT DEPT,
SUBSTR(NAMES, 1, LENGTH(NAMES) -1)
FROM (SELECT
DEPT,
REPLACE
(REPLACE
(XMLSERIALIZE
(CONTENT XMLAGG(XMLELEMENT(NAME A, NAME)
ORDER BY NAME)
AS VARCHAR(60)), '<A>', ''), '</A>', ',') AS NAMES
FROM EMPLOYEE GROUP BY DEPT) AS X;

DEPT NAMES
----- --------------------
SOLUTIONS BERNI, MISO, SERGE
DEVELOPMENT JOHN
L3 JACK, LEE
ID MARK
QUALITY LILY

HTH,

--Jeff

[meter_man]

Thanks you both so much. I have never even heard of recursive queries and
XMLAGGREGATE! I will be checking this out.
:)
"jefftyzzer" <jefft...@sbcglobal.net> wrote in message
news:1161986396.8...@b28g2000cwb.googlegroups.com...