I have a relatively complex SELECT where some of the returned fields are dynamically generated and some are composed of the resultset of the sub-query itlsef.
In this case, I have a models table which needs to be queried and it's results MUST be returned as a single string value.
[sql]SELECT
name,
location,
(
SELECT * FROM models WHERE id_department = 12
) AS `Model`
FROM[/sql]
The 'Model' field currently results in several records (potentially) being returned which breaks the query, I wonder if Access supports some resultset concatenation?
I have achieved a similar result by implementing an custom method which is then called similar to SQL aggregate functions:
[sql]SELECT
name,
location,
ConcatModels(12) AS `Model`
FROM[/sql]
However this requires implementing yet another method and I'm curious to know whether Access SQL supports some method to do just this?
I know you can concat strings togather using the & operator but because the resultset varies in record count this static approach will not work.
Cheers,
Alex
MS Access SQL - concat sub-query result as a string
Moderator: General Moderators
-
alex.barylski
- DevNet Evangelist
- Posts: 6267
- Joined: Tue Dec 21, 2004 5:00 pm
- Location: Winnipeg