sql - select partially unique values and concatenate non unique values into string

Question

Say I have the following data set:

A   B    C
--- ---- ---
1   aaa  1
1   aaa  2
1   aaa  5
1   aaa  8
2   bbb  2
2   bbb  4
3   ccc  1
4   ddd  2
5   aaa  2
6   bbb  0
6   bbb  9

How can I group by columns A and B and return a third column that is all unique values of C for that group concatenated together (in a comma separated string) using SQL.

For clarity this is the resultant data set I am looking for:

A   B    C_Concat
--- ---- ---
1   aaa  1,2,5,8
2   bbb  2,4
3   ccc  1
4   ddd  2
5   aaa  2
6   bbb  0,9

I am currently querying this in MS access, but ideally would be nice if there would be a common solution for all SQL databases.

Note: In this use case the column C only has 10 known values being 0-9

Could you please specify RDBMS you use. String-aggregation is vendor-specific feature — Sergey, Apr 20 '22 at 07:14
Yeah I'm using MS Access (maybe unfortunately), but was hoping this would not have to be restricted to one vendors implementation! Obviously if that is all that is possible thats fine - I guess I don't know enough hence the question! — maloo, Apr 20 '22 at 07:23
Does this answer your question? [ConcatRelated function in a query](https://stackoverflow.com/questions/18940038/concatrelated-function-in-a-query) — June7, Apr 20 '22 at 08:07
Thanks, I'll look into using vba, I was hoping this could be done in SQL though... — maloo, Apr 20 '22 at 08:28

maloo · Accepted Answer · 2022-04-21T06:11:28.473

So using the ConcatRelated function did not really solve my problem as it appeared to be quite inefficient and caused the already slow MS access (with a large dataset) to grind to a halt.

I was able to do this in SQL using some sub queries for my use case - As added/noted above the column C consists on a well defined data set.

For my use case the following SQL (and concatenation) in MS Access worked:

SELECT A, B, left(C_tmp, len(C_tmp) - 1) AS C_Concat
  FROM (SELECT qry.A, qry.B,
    (SELECT "0," FROM Table WHERE PointNumber=qry.A and C=0) & 
    (SELECT "1," FROM Table WHERE PointNumber=qry.A and C=1) & 
    (SELECT "2," FROM Table WHERE PointNumber=qry.A and C=2) & 
    (SELECT "3," FROM Table WHERE PointNumber=qry.A and C=3) & 
    (SELECT "4," FROM Table WHERE PointNumber=qry.A and C=4) & 
    (SELECT "5," FROM Table WHERE PointNumber=qry.A and C=5) &
    (SELECT "6," FROM Table WHERE PointNumber=qry.A and C=6) & 
    (SELECT "7," FROM Table WHERE PointNumber=qry.A and C=7) & 
    (SELECT "8," FROM Table WHERE PointNumber=qry.A and C=8) & 
    (SELECT "9," FROM Table WHERE PointNumber=qry.A and C=9)
  AS C_tmp
FROM (SELECT A, B FROM Table GROUP BY A, B) AS qry) AS qry2;

Other alternatives I looked at was exporting all data/tables into another database that supported string aggregation. This would provide a much more universal solution where the contents of the C column were not known. I believe the solutions here would be (please feel free to expand this list if it helps):

Mariadb: Use group_concat

Oracle 11g R2 and above: Use listagg

Oracle below 11g R2: Use wm_concat

Hopefully this helps someone...

sql - select partially unique values and concatenate non unique values into string

1 Answers1