Combine text from multiple rows into a single row (as a comma separated string)

Question

I'm trying to accomplish the same thing that has been answered here: SQL combining data from two rows into a single row when they share a common identifier only in MS Access.

Here's what the data looks like:

ArticleID     CategoryName
1             Alpha
1             Beta
2             Beta
2             Gamma
3             Alpha
3             Delta

I want the results to look like this:

ArticleID     Categories
1             Alpha, Beta
2             Beta, Gamma
3             Alpha, Delta

The general/common term for this is "group concat". And the ways of achieving such vary greatly .. no idea how/if Access allows it. — user2864740
– user2864740, Commented Jun 23, 2015 at 1:08
You would need to use VBA, which is not available outside of MS Access front end. Here is one example stackoverflow.com/questions/92698/combine-rows-concatenate-rows/…, there are others. — Fionnuala
– Fionnuala, Commented Jun 23, 2015 at 6:44

Greg Viers · Accepted Answer · 2015-06-23 01:48:35Z

1

The short answer is no. The ways you would implement this well in other DMBS are not available in MS Access Jet SQL. You would need to approach this an Access VBA programming issue, not a SQL one. And yes, the performance would be much worse than with a SQL solution. If you google for a VBA solution, there are those out there who have attempted it before. Personally, I would be seriously questioning how strongly this requirement is needed for your reporting. If you are generating a grouped report in SQL, you can list the results under in more of a tab format for the end user with a similar effect:

1
    Alpha
    Beta
2
    Beta
    Gamma
3
    Alpha
    Delta

answered Jun 23, 2015 at 1:48

Greg Viers

3,5233 gold badges22 silver badges36 bronze badges

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

2 Comments

Fionnuala Over a year ago

Agreed. It does not seem sensible to remove normalization.

RobM Over a year ago

I understand the desire to maintain normalization. In my case I have received a couple of CSV files from a generic CMS and I'm importing them into WordPress. I don't have direct access to the database so I have to use a plugin to get the posts in the system.

Collectives™ on Stack Overflow

Combine text from multiple rows into a single row (as a comma separated string)

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related