Returning the concatenated values in column 'X'

[Guest_imported]

Is it possible in Jet SQL to create a query (using a sub-query if necessary) that will return the concatenated values in Column 'X' from all rows in a single field.

For example, table = tblColors

Number Color
====== =====
1 Blue
2 Green
3 Yellow

SELECT tblColors.Color AS AllColors

Result wanted:

AllColors
=========
BlueGreenYellow

Result not wanted:

AllColors
=========
Blue
Green
Yellow

Reason: I want to see if it's possbile to have a Jet SQL query generate an .XML file. The way I was intending was to have the parent query return (XML Header) + (Sub-query: returning a string consisiting of individual tag/data iterations) + (XML footer).

The problem is that instead of getting 1 record returned by the parent query with (header)+(data x 'n')+(footer) I get multiple records returned with (header) + (data x 1) + (footer). Each of which is well formed .XML by the way.

I know I could do this in T-SQL using variables and looping to build up the result, but I would like to know whether it's possible in Jet-SQL.

Thanks

 

[blom0344]

I don't think straight SQL can do it for you, but with a limited amount of records you can work around it using a crosstab-query and a query than runs on the crosstab one:

With 3 records:

crosstab query (saved as Jumbocross):

TRANSFORM First([JumboJet].[Color]) AS FirstOfColor
SELECT 1 AS Expr
FROM JumboJet
GROUP BY 1
PIVOT [JumboJet].[Number];


second query:

SELECT [1] & [2] & [3] AS Expr1
FROM Jumbo_cross;

Of course this will not work with large amounts of data !!


T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[IanAsturias]

Thanks for the quick response.

The solution wouldn't work in this case because it would potentially need to work for thousands of records. Nevertheless it's a useful technique to know.

Thanks again
Ian

 

[cmmrfrds]

You probably need to do something similiar as in T-SQL.

Are you familiar with the ADO recordset? The GetRows method will return the recordset as columns and rows (2 dimensions) note this is counter intutitive where the column is the first deminsion. It might be a good solution to do something like the following.

Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
dim VarArray as Variant
dim str1 as string, indx as integer

Set rs = New ADODB.Recordset
Set cn = CurrentProject.Connection

sql1 = "select id, name from dbo.Employees"
rs.Open sql1, connString, adOpenStatic, adLockOptimistic

VarArray = rs.GetRows
'- string names together
for indx = 0 to rs.RecordCount -1
str1 = str1 & VarArray(2, indx) & " "
next
debug.print str1

 

[blom0344]

Hello cmmrfrds,

Suppose I modify the original question into this one

table = TBL

ITEM OPTION
X Blue
X Red
Y Yellow
Y Pink
Y Black


Now I want the options concatenated into a string, with "," as separator, but now for each ITEM resulting in:

ITEM STRING

X Blue,Red
Y Yellow,Pink,Black

How would that be in an ADO solution and how would I call it to give me a new output table?

Thanks in advance!! T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[cmmrfrds]

First thought and untested code.

Create 2 Recordsets. Assume variables in previous post.
Dim cn As New ADODB.Connection
Dim rs1 As New ADODB.Recordset, rs2 as New ADODB.Recordset
dim sql1 as string
Set cn = CurrentProject.Connection 'assume current db

sql1 = "select ITEM, STRING From table;"
sql1 = sql1 & " Select ITEM from table group by ITEM;"

rs1.Open sql1, cn, 3, 3

Set rs2 = rs1.NextRecordset
'Loop through rs2 for each item
rs2.movefirst
While not rs2.EOF
rs1.Filter = "ITEM = " & rs2!ITEM

VarArray = rs1.GetRows
'- string names together
Add a comma and strip off with a left function at end.
for indx = 0 to rs1.RecordCount -1
str1 = str1 & VarArray(2, indx) & ","
next
str1 = Left(str1,1)
debug.print rs2!item; " "; str1
rs1.Filter = adFilterNone
rs2.movenext
Wend