0

I'm trying to concatenate column values when grouping in Microsoft Access using this code but keep getting a syntax error on the SELECT function inside STUFF.

Start with this table t1

fname | lname | Program | Site | Salary
------+-------+---------+------+-------
John  | Smith |    a    |   1  |  10
John  | Smith |    a    |   1  |  12
John  | Smith |    b    |   3  |  15
Grace | Jones |    b    |   3  |  15
Grace | Jones |    b    |   3  |  15
Bob   | Green |    a    |   7  |  10

and get this

fname | lname | Program | Site | Salary
------+-------+---------+------+-------
John  | Smith |    a    |   1  |  10, 12
John  | Smith |    b    |   3  |  15
Grace | Jones |    b    |   3  |  15
Bob   | Green |    a    |   7  |  10

Here is the code I've been trying.

SELECT fname, lname, Program, Site, 
   STUFF((SELECT DISTINCT ',' + Salary 
          FROM t1
          FOR XML PATH('')),1,1,'') AS Salaries

FROM t1
GROUP BY  fname,lname,Program,Site;
June7
  • 19,874
  • 8
  • 24
  • 34
Keanu McDonough
  • 1
  • Edit question to show code for STUFF function. – June7 Jul 21 '20 at 22:15
  • Why are you concatenating comma with Salary? What is `FOR XML PATH('')`? – June7 Jul 21 '20 at 22:26
  • 1
    ``FOR XML`` syntax is only for SQLServer, it cannot be used in Access SQL. See workaround: https://stackoverflow.com/questions/21380730/ms-access-equivalent-of-for-xml-path-in-t-sql-to-list-related-values – kamocyc Jul 22 '20 at 03:27

0 Answers0