> -----Original Message-----
> From: Richard White [mailto:[EMAIL PROTECTED]
> Sent: Sunday, May 04, 2008 1:35 PM
> To: CF-Talk
> Subject: combining query column rows
>
> hi
>
> i have a query that returns x amount of columns (i wont always know how
> many) such as follows:
>
> col1 | col2 | col3
> ------------------
> 1 | 2 | 3
>
> i need to run some code that combines the columns into one string, and
> each value seperated by a space.
>
> so that a string would then equal "1 2 3"
I'm reading this two ways... so here are two answers: ;^)
1) If you want the values of a column (a single column) then the ValueList
function will do this. But I don't think that's what you want.
2) If you want the value of each row in the query as a single value then
there are several ways (and several ways to combine these ways depending on
how you need to do things):
+) As you already noted you can loop over the query using the
"ColumnList" (queryname.ColumnList will give you the list of column names)
as your list in a CFLOOP. Use array notation to get the values (something
like "queryname[CurrentColumnName][RowNumber]").
+) If possible it's probably easiest to do this in the original
query. Depending on your DB you can concatenate multiple columns into a
single return... something like SELECT COL1 + " " + COL2 + " " + COL3 AS
MyValue.
+) If you can't do it in the original query you might (should?) be
able to do it as a query of queries. I'm just not sure if concatenation
works in QoQ in this way (it does in others) but it would make the whole
mess pretty simple. You could grab the ColumnList as above, replace the
commas with the concatenation and generate the SQL you need use. For
example the ColumnList:
Col1,Col2,Col3
Could become, using a single replace of all commas:
Col1 + " " + Col2 + " " + Col3
You can then build your SQL for the QoQ out of that (if it works).
If the columns are consistent for some time (or multiple queries) you can
also do this with the database itself - you need an initial query to get you
the column list but then all your queries for data could use the
concatenation to return the value as you want it.
+) In the end what you really want is a pivot query: the same data
you have "turned" 90 degrees such that ValueList() will return exactly the
information that you want. If you're getting a lot of rows initially then
you could pivot the query in CF (there are UDFs out there to do it) but
again, if your DB supports this then you should do it there - SQL Server
2005 supports a "PIVOT" clause for example as do many others although they
all have their own names (Access calls this "Crosstab Queries" for example).
We could probably be more specific if you told us what DBMS you're using,
how many rows you'll be returning and how often you'll be doing this. In
the end, unless this is a performance hotspot then the simplest method
(looping over the column list and creating the value) is probably going to
be the fastest to develop/test and easiest to maintain.
Jim Davis
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:304729
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
