Re: Grouping results horizontally in SQL -- MIDRANGE-L

While a UDF like Birgitta's will work...you'll end up reading the
table multiple times....

A better solution would be to make use of the SQL scratch pad so that
you don't have to read through the table but once...
Here's one example:
http://www.itjungle.com/mpo/mpo042403-story02.html

Only sticking point that I can thin of is that I'm not sure off the
top of my head if you can control the order that the order# are
listed...

Rob's suggestion of a hierarchical query would be easier if you can
get it to work.

Charles

On Tue, Jul 3, 2012 at 12:44 PM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> wrote:

You may write a small UDF something like this:

Create Function MySchema/ListOrder (
ParCustNo Integer))
Returns VarChar(1024)
Language SQL
Not Deterministic
Reads SQL Data
Called on NULL Input
Disallow Parallel
BEGIN
Declare ReturnVal VarChar(1024) Not NULL Default ' ' ;

For CsrC1 as C1 Cursor
For Select OrderNo
From OrderHeader

Where OrderNo = ParOrderNo

Do Set ReturnVal = ReturnVal Concat
case When ReturnVal > ''
Then ', ' else '' End
Concat VarChar(CsrC1.OrderNo);
End For ;

Return LTrim(ReturnVal);
END ;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jonathan Mason
Gesendet: Tuesday, 03.7 2012 15:54
An: midrange-l@xxxxxxxxxxxx
Betreff: 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.

--
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.