MS Access SQL - concat sub-query result as a string

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

MS Access SQL - concat sub-query result as a string

Post by alex.barylski »

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
Post Reply