RE: SQL , multi row note file.

[vhamberg@xxxxxxxxxxx]

Crosstabs and pivot tables with numeric data are fairly simple, involving CASE and aggregates like SUM - several examples if you google for it. But text can't be aggregated - least not with CONCAT that I know of - there are also several examples through google on dynamic pivot tables using stored procedures. There is also a PIVOT function in recent SQL Server 2005 - is there any of this in the most recent SQL on iSeries? Again, CONCAT does not seem part of the picture.

-------------- Original message -------------- 
From: "Hatzenbeler, Tim" <thatzenbeler@xxxxxxxxxxxxx> 

> I like the UDF route, and that might be the best route in this scenario. 
>
> Thanks, Tim 
>
> -----Original Message----- 
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] 
> On Behalf Of Wilt, Charles 
> Sent: Thursday, May 29, 2008 5:04 AM 
> To: Midrange Systems Technical Discussion 
> Subject: RE: SQL , multi row note file. 
>
> Tim, 
>
> Anything is possible...with enough effort, time, and/or money! 
>
> In you case, what you need is a User Defined Function (UDF) that returns a 
> string containing the notes 
> concatenated together. 
>
> You could write the UDF in either SQL or RPG; then it'd simply be a matter of 
> select po#, concatNotesUDF(po#) 
> from potable 
>
> A good place to learn about UDFs is this redbook, "Stored Procedures, Triggers, 
> and User-Defined 
> Functions on DB2 Universal Database for iSeries" 
> http://www.redbooks.ibm.com/abstracts/sg246503.html?Open 
>
>
> HTH, 
>
>
> Charles Wilt 
> -- 
> Software Engineer 
> CINTAS Corporation - IT 92B 
> 513.701.1307 
>
> wiltc@xxxxxxxxxx 
>
>
> > -----Original Message----- 
> > From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l- 
> > bounces@xxxxxxxxxxxx] On Behalf Of Hatzenbeler, Tim 
> > Sent: Wednesday, May 28, 2008 7:30 PM 
> > To: 'Midrange Systems Technical Discussion' 
> > Subject: SQL , multi row note file. 
> >
> > I this possible? 
> >
> > I have a "note" table, that may or may not have data. 
> >
> > For example. 
> >
> > Po#,note_txt,note_seq#. 
> > ---------------------------- 
> >
> > Po123,this item is red and,1 
> > Po123,has pink dots,2 
> > Po123,please only order on christmas,3 
> > Po456,I'm green,1 
> >
> >
> > Po table 
> > ------------- 
> > Po#,customer,address 
> > Po123,tim,Spokane 
> > Po456,fred,color: green 
> >
> > I would like a query that would show me, 
> >
> > Po123,tim,Spokane,this item is red and has pink dots please only order on 
> > Christmas. 
> > Po456,fred,fargo,color: green 
> >
> >
> > Is that at all possible? 
> >
> > Thanks, Tim 
> >
> >
> >
> > Think Green! Please do not print this e-mail unless you need to. Thank 
> > you. 
> >
> >
> > -------------------------------------------------------------------------- 
> > ---- 
> > This e-mail message, including any attachments, is for the 
> > sole use of the intended recipient(s) and may contain 
> > confidential or privileged information. Any unauthorized 
> > review, use, disclosure or distribution is prohibited. If 
> > you are not the intended recipient, please contact the 
> > sender by reply e-mail and destroy the message. 
> >
> >
> > ========================================================================== 
> > ==== 
> >
> > -- 
> > 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 e-mail transmission contains information that is intended to be 
> confidential and privileged. If you receive this e-mail and you are not a named 
> addressee you are hereby notified that you are not authorized to read, print, 
> retain, copy or disseminate this communication without the consent of the sender 
> and that doing so is prohibited and may be unlawful. Please reply to the 
> message immediately by informing the sender that the message was misdirected. 
> After replying, please delete and otherwise erase it and any attachments from 
> your computer system. Your assistance in correcting this error is appreciated. 
> -- 
> 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. 
>
>
>
> Think Green! Please do not print this e-mail unless you need to. Thank you. 
>
>
> ------------------------------------------------------------------------------ 
> This e-mail message, including any attachments, is for the 
> sole use of the intended recipient(s) and may contain 
> confidential or privileged information. Any unauthorized 
> review, use, disclosure or distribution is prohibited. If 
> you are not the intended recipient, please contact the 
> sender by reply e-mail and destroy the message. 
>
>
> ============================================================================== 
>
> -- 
> 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.