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 with the most votes on Stackoverflow is putting formatting in SQL rather than in the front end. — onedaywhen
– onedaywhen, Commented Oct 13, 2011 at 8: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
– Fionnuala, Commented Oct 13, 2011 at 9:12

Fionnuala · Accepted Answer · 2011-10-13 09:31:14Z

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, 2011 at 9:31

answered Oct 13, 2011 at 8:25

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JeffO · Accepted Answer · 2011-10-13 15:35:35Z

0

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.

answered Oct 13, 2011 at 15:35

JeffO

8,1233 gold badges46 silver badges55 bronze badges

Comments

RickM · Accepted Answer · 2018-03-24 00:20:45Z

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.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related