[thelist] [SQL] tough concatenation

[rudy]

> 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