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

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

Answer

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…