[thelist] [SQL] tough concatenation
rudy
r937 at interlog.com
Fri Apr 13 18:58:00 CDT 2001
> I want to get it to return:
>
> handle,body,gipode,carpet
>
> through SQL. I do *NOT* want to iterate
> through the results and make my own string.
hi matt
why *not* iterate through the results? in cold fusion, you simply let
cfoutput do the iteration for you...
<cfset s="">
<cfoutput query="q">
<cfset s=ListAppend(s,q.widgetparts)>
</cfoutput>
<cfoutput>#s#</cfoutput>
... and i'm sure there has to be something similarly simple in asp
okay, strictly as an academic exercise, can you imagine a query result set
where all the columns all have different values all on the same row? i can
imagine four tables, and a query that joins them together
now imagine if they were all the same table, i.e. a 4-way self-join
for brevity, i'll use w instead of widgetparts as the column name --
select a.w, b.w, c.w, d.w
from theTable a, theTable b, theTable c, theTable d
notice the absence of join conditions -- a full cartesian product
the query result set has 256 rows and looks like this --
handle,handle,handle,handle
handle,handle,handle,body
handle,handle,handle,gipode
etc., all the way to
carpet,carpet,carpet,carpet
okay, now you really only want one row -- specifically, one where all the
column values are different, except there are 24 of those, so pick the one
that has them in ascending sequence...
select a.w, b.w, c.w, d.w
from theTable a, theTable b, theTable c, theTable d
where a.w > b.w and b.w > c.w and c.w > d.w
voila
inefficient as heck, but it should work
oh yeah, you'll have to fart around with making a concatenated string out
of the four columns, using sql functions, something like
trim(a.w) || ', ' || trim(b.w) || ', ' || trim(c.w) || ', ' || trim(d.w)
did i say four columns? uh, what if the widget table has seventeen rows,
doesn't that mean you'll need a seventeen-way join?
right
i'm guessing wildly, but maybe there's a way in SQL3 that you could write a
recursive query, but i wouldn't put any money on it being efficient...
okay, now what was the problem again about stringing them together in asp?
=o)
rudy
More information about the thelist
mailing list