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