Create a comma-separated string from multiple rows of one column?

Question

I've an access table like this

ID | UserName | CarBrand
-------------------------
0    Peter      VW
1    Peter      Ferrari
2    Mike       Audi
3    Peter      Dodge
4    Heidi      BMW
5    Heidi      Ford

I need the names from the CarBrand field as a comma separated list for a report.

Is there a way (without VB, maybe using a COALESCE alternative?) to create a comma-separated string like this, without the Name: part?

Peter: VW, Ferrari, Dodge
Mike:  Audi
Heidi: BMW, Ford

As it's for a report are there any other ways to do this, maybe using expressions in the report?

The proposed table would not be not first normal form. Consider using a report or other 'front end' tool for display. Also consider that the [most common SQL antipattern](http://stackoverflow.com/questions/346659/what-are-the-most-common-sql-anti-patterns/346850#346850) with the most votes on Stackoverflow is putting formatting in SQL rather than in the front end. — onedaywhen, Oct 13 '11 at 08:27
I do not think there is an easy way to do this in a report either, you will still need to use a VBA function. — Fionnuala, Oct 13 '11 at 09:12

Fionnuala · Accepted Answer · 2011-10-13T09:31:14.130

8

You cannot do this is Access without VBA. Coalesce does not exist, but you can write a UDF that has some of the functionality, for example http://allenbrowne.com/func-concat.html However, once you use a UDF, the query is no longer viable outside Access.

edited Oct 13 '11 at 09:31

answered Oct 13 '11 at 08:25

Fionnuala

90,370
7
114
152

score 0 · Answer 2 · edited Mar 24 '18 at 00:20

If you do not need every single value in your comma delimited list, but say just up to the first 3 or 4 or so, then there is a pure SQL solution in Access.

I am working on a DB for a non-profit that has Tutors and Classes. For most classes, there is just 1 or 2 tutors. For display purposes, I can't list more than 2 or 3 anyway, so I am not worried about truncating outliers with 5 or more tutors.

This will grab the 3 tutors for each class with the lowest Tutor IDs

Select JTC1.ClassID, Min(JTC1.TID1) as TutorID1,
  Min(JTC1.TID2) as TutorID2,
  Min(JTC1.TID3) as TutorID3 
from (
  Select distinct TC1.ClassID,
    TC1.TutorID as TID1,
    TC2.TutorID as TID2,
    TC3.TutorID as TID3 
  from ((
   Classes C 
   Left Join TutorClasses TC1 
     on C.ClassID = TC1.ClassID)
   Left Join TutorClasses TC2 
     on TC1.ClassID = TC2.ClassID and TC1.TutorID < TC2.TutorID
   )
   Left Join TutorClasses TC3
     on TC2.ClassID = TC3.ClassID and TC2.TutorID < TC3.TutorID
   ) as JTC1
Group by JTC1.ClassID

Obviously, 1 extra step (not shown) will be needed to combine the three columns into 1.

score 0 · Answer 3 · answered Oct 13 '11 at 15:35

Create a main report with a unique list of usernames.

Create a sub report with a list of usernames and their Car Brands. Join the forms on the UserName. In the design of the subform, use 4-5 columns which print horizontally. You can have the CarBrand field include a formula like =[CarBrand] & ", " (Sorry the last one is going to have an unecessary comma. They will be spaced accross uniformly and will break into a new row if a particular user has more brands than can fit accross you report (Which will be real hard to do if you just create one large comma-separated string.).

No VBA involved at all.

Create a comma-separated string from multiple rows of one column?

3 Answers3