0

I am trying to concatenate values in a column when those values correspond to the same ID number. Here's an example of the data:

enter image description here

The query result I want is this:

enter image description here

Here is the code I tried to achieve this result.

SELECT column1, 
       STUFF((SELECT ', ' + column2
              FROM your_table t2
              WHERE t1.column1 = t2.column1
              FOR XML PATH('')), 1, 2, '') AS concatenated_values
FROM your_table t1
GROUP BY column1;

This code returns question marks (e.g., "????") in many rows. I don't want this. Please help.

Michael Covell
  • 55
  • 5
  • 2
    ???? normally would indicate that the data is `nvarchar` and somewhere along the way you are converting it as `varchar` and it doesn't support all the characters. But you haven't given us enough code/explanation – Martin Smith May 16 '23 at 16:57
  • DECLARE @t TABLE (id INT, names VARCHAR(5)) INSERT @t (id,names) VALUES (1, 'A'),(1, 'B'),(2, 'A'),(3, 'B') SELECT id, STRING_AGG(Names, ', ') FROM @t GROUP BY id; – Power Mouse May 17 '23 at 17:35
  • I suspect selecting elements using FOR XML PATH returns NVARCHAR strings, not VARCHAR. If you can't make your database contain NVARCHAR, try converting the results with [(... FOR XML PATH ... TYPE).value('.', 'varchar(max)')](https://stackoverflow.com/a/31212041/22437). – Dour High Arch Jun 10 '23 at 13:34

0 Answers0