Taking Rob's suggestion of a hierarchical query, I wrote up a recursive
CTE that kind of works. It produces more records than you need, but I
don't have the time to figure those out today.
DROP TABLE TABLE5
;
CREATE TABLE table5( account DECIMAL(4,0),
year decimal(4,0),
order char(5))
;
insert INTO TABLE5 VALUES
(1000, 2012, '10001'),
(1000, 2012, '11023'),
(1000, 2012, '12700'),
(1000, 2012, '13508'),
(2000, 2012, '1255')
;
SELECT * FROM TABLE5
;
With t1 as
(select account, year, order from table5
),
t2 (account, year, order)
as (select account, year, min(order) from table5
group by account, year ),
r1 (r_account, r_year, r_order, counter) as
(select t2.*, '' from t2
UNION ALL
Select account, year,
cast(r1.r_order || ', ' || order as varchar(200)), order
from t1,
r1 where r1.r_account = t1.account and r1.r_year = t1.year
and order > counter
)
search depth first by r_account, r_year set seqcol
SELECT * FROM R1 order by seqcol
Produces:
R_ACCOUNT R_YEAR R_ORDER
1,000 2,012 10001
1,000 2,012 10001, 10001
1,000 2,012 10001, 11023
1,000 2,012 10001, 12700
1,000 2,012 10001, 13508
1,000 2,012 10001, 10001, 11023
1,000 2,012 10001, 10001, 12700
1,000 2,012 10001, 10001, 13508
1,000 2,012 10001, 11023, 12700
1,000 2,012 10001, 11023, 13508
1,000 2,012 10001, 12700, 13508
1,000 2,012 10001, 10001, 11023, 12700
1,000 2,012 10001, 10001, 11023, 13508
1,000 2,012 10001, 10001, 12700, 13508
1,000 2,012 10001, 11023, 12700, 13508
1,000 2,012 10001, 10001, 11023, 12700, 13508
2,000 2,012 1255
2,000 2,012 1255 , 1255
-Tom Stieger
IT Manager
California Fine Wire
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jonathan Mason
Sent: Tuesday, July 03, 2012 6:54 AM
To: 'midrange-l@xxxxxxxxxxxx'
Subject: Grouping results horizontally in SQL
Hi All
I'm sure this must have been asked before, but I can't for the life of
me find anything in the Archives, so my apologies if that's the case.
I have an order header file with a separate record for each
account/year/period key combination. For example
Account Year Order
------- ---- -------
1000 2012 10001
1000 2012 11023
1000 2012 12700
1000 2012 13508
2000 2012 1255
Ideally I would like to see the results as shown below:
Account Year Orders
------- ---- --------
1000 2012 10001, 11023, 12700, 13508
2000 2012 1255
I have been looking online and can see solutions that might help, but
they are for different flavours of SQL and use functionality that isn't
present within our version (v6r1),of SQL.
Has anybody come across a method of doing this?
Thanks
Jonathan
Proud partner of The Ageas Bowl and the Ageas Salisbury International
Arts Festival.
Registered Address: Ageas House Tollgate Eastleigh Hampshire SO53 3YA
Registered Number: 354568 England
Authorised and regulated by the Financial Services Authority
This e-mail together with any attachments are intended for the addressee
only and may be private and confidential. If you are not the intended
recipient, or the person responsible for delivering it to the intended
recipient, you must not open any attachments, or copy, disclose,
distribute, retain or use this e-mail, including any attachments, in any
way whatsoever; please return it to us immediately using the reply
facility on e-mail.
Consider the environment and think before you print this email.
--
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.
midrange.com
