0

I am trying to combine rows in table for my query. All information is the same for each field but "order comments". I want to combine the duplicate rows and have the order comments all in one row like as:

Table name : Orders

Order number Name Date Order Comments
123 John 5.2 Careful
123 John 5.2 Call Before
456 Dave 6.3 Asap
456 Dave 6.3 Call Before
Order number Name Date Order Comments
123 John 5.2 Careful, Call Before
456 Dave 6.3 Asap, Call before

Tried:

SELECT ORDER NUMBER, NAME, DATE, ORDER COMMENTS, string_agg(isnull(Order Comments, ' '),', ') AS ORDER COMMENTS
FROM Orders 
GROUP BY ORDER NUMBER, NAME;

Result:

wrong number of arguements used with function in query expression "string_agg(convert(nvarchar(max), isnull(order comments,'n/a')),',"

James Z
  • 12,209
  • 10
  • 24
  • 44
V.TX
  • 1
  • 1
  • 1
    It's a very long time ago since I've used Acces, but I remember you have to enclose your field names, when containing spaces, in square brackets. So try `[Order Comments]` for a start. – Roemer Jul 20 '23 at 19:11
  • Hi, when you say containing spaces, you mean the space between order and comments? I tried the [] brackets and then I also changed the field to just "comments" and it had the same result. – V.TX Jul 20 '23 at 19:20
  • Yes and also `[Order number]`. A space is a separator of key words, so you cannot use it in field names without making sure the interpreter understands it is part of the field name. Hence the `[]` – Roemer Jul 20 '23 at 19:24
  • `SELECT [ORDER NUMBER], NAME, [DATE], string_agg(isnull([Order Comments], ' '),', ') AS [ORDER COMMENTS] FROM Orders GROUP BY [ORDER NUMBER], NAME, [DATE];` – Roemer Jul 20 '23 at 19:25
  • Also: Date may very well be a keyword so put brackets around it too, just to be sure. Can't harm. – Roemer Jul 20 '23 at 19:26
  • These fields are just placeholders for my actual values. My actual fields are arbitrary combination of letters&numbers like "SLCFGC2" so there are no spaces in the field name. I tried the [] after the is null and it gave me a syntax error. – V.TX Jul 20 '23 at 19:37
  • STRING_AGG and CONVERT are not supported in Access SQL. Add tag for RDBMS used to store data if not Access. Also review https://stackoverflow.com/questions/92698/combine-rows-concatenate-rows/93863#93863 and https://stackoverflow.com/questions/13278590/combine-values-from-related-rows-into-a-single-concatenated-string-value – June7 Jul 20 '23 at 20:34

0 Answers0