Transform and concatenation of column data

[Guest]

Hello;

I have a tranform query which shows:

Row: Col1 Col2 Col3 Col4 [Concatonation]
Fail: Rpt1 Rpt60 RptA Etc. Fail: Rpt1, Rpt60, RptA,

Is there a way to show the data values in a single column, as if
concatenating? I can build the concatenation expression "by hand" (manually
enter each column ref in the concatenation expression) but am looking for
some way to do this in an automated fashion as the columns will vary with
each query run, and I don't want to go in and manually edit the concat
expression. If this can be done in SQL 2005, then that would be better.

thanks, G

 

[Duane Hookom]

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This
won't work in SQL 2005.

 

[Guest]

Hi Duane:

Thanks! It works beautifully. I've been using it since this original post,
and am just now catching up with this resopnse. The one thing I note is that
one cannot then do an aggregate against a query utilizing the result of this
function. SO, one needs to create a new table from the query with the
concatenation results and go from there. Very useful nonetheless.

Thx!

~G

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This
won't work in SQL 2005.

--
Duane Hookom
MS Access MVP

Hello;

I have a tranform query which shows:

Row: Col1 Col2 Col3 Col4 [Concatonation]
Fail: Rpt1 Rpt60 RptA Etc. Fail: Rpt1, Rpt60, RptA,

Is there a way to show the data values in a single column, as if
concatenating? I can build the concatenation expression "by hand"
(manually
enter each column ref in the concatenation expression) but am looking for
some way to do this in an automated fashion as the columns will vary with
each query run, and I don't want to go in and manually edit the concat
expression. If this can be done in SQL 2005, then that would be better.

thanks, G