RE: SQL Recursive Common Table Experssion

Hi Glenn,
recursion is pretty neat and very powerful. Here a sample using data like you posted.

create table qtemp.serial_master (order#, line#, serial#) as (values(integer(123456), integer(1), char('abc',3)),(integer(123456), integer(2), char('def',3)),(integer(123456), integer(3), char('ghi',3))) with data
insert into qtemp.serial_master(values(integer(234567), integer(1), char('abc',3)),(integer(234567), integer(2), char('def',3)),(integer(234567), integer(3), char('ghi',3)))
insert into qtemp.serial_master(values(integer(345678), integer(1), char('jkl',3)),(integer(345678), integer(2), char('mno',3)),(integer(345678), integer(3), char('pqr',3)))
with recursive serial_masters (order#, serial#s, line#) as (selectorder#, cast(serial# as varchar(96)), line#fromqtemp.serial_master where line# = 1union allselecta.order#, b.serial#s concat ':' concat a.serial#, b.line#+1fromqtemp.serial_master as ajoinserial_masters as bon a.order# = b.order#and b.line#+1 = a.line#)selectorder#,1 as line#,serial#sfromserial_masters as awhereline# = (select max(b.line#) from serial_masters as b where a.order# = b.order#)order byorder#, line#
OutputORDER#LINE#SERIAL#S
1234561abc:def:ghi
2345671abc:def:ghi
3456781jkl:mno:pqr

Jim

Date: Tue, 30 Sep 2014 11:02:08 -0400
Subject: SQL Recursive Common Table Expression
From: glenn.gundermann@xxxxxxxxx
To: midrange-l@xxxxxxxxxxxx

Hi all,

I'm havnig some difficulties writing a recursive CTE to concatenate a text
field together from multiple rows.
I'm wondering if anyone can help.

I have tables ORDER_HEADER, ORDER_DETAILS, and SERIAL_MASTER.
One header can have multiple details and one detail can have multiple
serial #s.
A single order line item can have more than one machine and hence the
serial # masterfile has multiple rows with the same order# and line item#.

Example of data in SERIAL_MASTER:

Order# Line# Serial#
123456 1 ABC
123456 1 DEF
123456 1 GHI

I would like to produce the following:

Order# Line# Serial #s
123456 1 ABC: DEF: GHI

Any assistance would be greatly appreciated.


Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (905) 795-2111 ext. 3098
Cell: (416) 317-3144
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.