Now that I have read my own e-mail, I realize that this may need to be fine
tuned - or cut up with a chain saw
If a po# has only one note, then this SQL will probably not give anything -
so you may also want to use coalesce
Like I said - DEFINITELY UNTESTED, but it may give you food for thought
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
Alan Shore
<AlanShore@xxxxxx
om> To
Sent by: Midrange Systems Technical
midrange-l-bounce Discussion
s@xxxxxxxxxxxx <midrange-l@xxxxxxxxxxxx>
cc
04/07/2010 11:27 Subject
AM Re: SQL Question: Combine Several
Rows Into One Column?
Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>
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.
--
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
