Re: SQL Question: Combine Several Rows Into One Column?

[Alan Shore <AlanShore@xxxxxxxx>]

How many notes can there be (maximum) per each PO#?
If the answer is unlimited, then scrap my idea, however, if there is a max
of lets say 6, then,
for example

DEFINITELY UNTESTED
select a.po#, a.note, b.note, c.note, d.note, e.note, f.note
from file1 a, file1 b, file1 c, file1 d, file1 e, file1 f
where
a.po# = b.po# and rrn(b) > rrn(a) and
b.po# = c.po# and rrn(c) > rrn(b) and
c.po# = d.po# and rrn(d) > rrn(c) and
d.po# = e.po# and rrn(e) > rrn(d) and
e.po# = f.po# and rrn(f) > rrn(e)


or something like that


Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill


                                                                       
             "Michael                                                  
             Naughton"                                                 
             <michael_naughton                                          To
             @juddwire.com>            "Midrange Systems Technical     
             Sent by:                  Discussion"                     
             midrange-l-bounce         <midrange-l@xxxxxxxxxxxx>       
             s@xxxxxxxxxxxx                                             cc
                                                                       
                                                                   Subject
             04/07/2010 10:28          Re: SQL Question: Combine Several
             AM                        Rows Into One Column?           
                                                                       
                                                                       
             Please respond to                                         
             Midrange Systems                                          
                 Technical                                             
                Discussion                                             
             <midrange-l@midra                                         
                 nge.com>                                              
                                                                       
                                                                       




Thanks to everyone who suggested pivot tables, but I'm afraid they won't
help me with this. The problem is that the values to be concatenated are
notes -- basically, we have a notes table with each note line being one
row, and the user wants to see all
the notes going horizontally across the page. So a better example of what
they want me to do is, given:

PO#     Note
  1        Ship the swimsuits
  1        to facilty in Tampa
  1        and the skis
  1        to our Calgary warehouse
  2        Confirmed by Irma
  2        this will ship Thursday

the user wants to see this:

PO#     Note
  1        Ship the swimsuits to facilty in Tampa and the skis to our
Calgary warehouse
  2        Confirmed by Irma this will ship Thursday



Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> writes:
> You might be able to write a UDF that would do it....
>
> You can do a pivot...
>    Sub Type       A    B    C    D    P   Q
> Type
> 1                       X    X     X    X
> 2                                                X    X
>
> Then maybe run the pivoted table through some field concatenations...
>
> But since the i doesn't have a built-in pivot function, you have to
> have a predefined set of values in sub-set to pivot on.
>
>
> HTH,
> Charles


Mike Naughton
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA  01376
413-676-3144
Internal: x 444
mnaughton@xxxxxxxxxxxx
****************************************
NOTICE: This e-mail and any files transmitted with it are confidential and
solely for the use of the intended recipient. If you are not the intended
recipient or the person responsible for delivering to the intended
recipient, be advised that any use is
strictly prohibited. If you have received this e-mail in error, please
notify us immediately by replying to it and then delete it from your
computer.

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