Concatonating Records Via SQL?

  • Thread starter Thread starter (PeteCresswell)
  • Start date Start date
P

(PeteCresswell)

Got tblPerson.

Got tblPhone.

Lots of phone numbers per person.

But I want a result set with one row per person - that shows all
of the phone numbers concatenated into a single column.

Whenever I've had to do this in the past, I resorted to a VBA
routine to pre-process the phone numbers or whatever into a work
table with one row per person and then joined to that work table.


Is this something than can be done in SQL without being abusive?
i.e. I don't want to get into doing something with SQL that will
bog down unduly when scaled.
 
Hi Pete,

I don't know of a SQL statement that can do this, but you can create a
concatenated list using a function that is called directly from the query,
without the need for a work table:

Return a concatenated list of sub-record values
http://www.mvps.org/access/modules/mdl0004.htm

Of course, this involves calling a function from within a query, which
likely won't scale so well. When you move to SQL Server as the BE database, I
think you would want to replace this type of function with a stored procedure
that ran on the server.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
Come SQL Server time you would want to replace this type of function
with a Pivot query.

-Tom.
 
Per Tom Wickerath said:
Of course, this involves calling a function from within a query, which
likely won't scale so well. When you move to SQL Server as the BE database, I
think you would want to replace this type of function with a stored procedure
that ran on the server.
Click to expand...

Thanks.

In a sense your solution scales better than mine bc it's still in
the SQL - and going to Server, would just involve re-writing that
piece rather than changing the structure of the whole process.
 
Is this something than can be done in SQL without being abusive?
i.e. I don't want to get into doing something with SQL that will
bog down unduly when scaled.
Click to expand...

Do Transform queries scale? In that case, you'd have a column for
each phone type (e.g., Home, Office, Mobile, etc.).
 
Back
Top