Concatenate Rows in SQL with Different values

Question

Hello I have an issue concatinating multiple rows in different tables adding a field separator ; For example. Basically I want to concatenate all values that has the same DocumentsID and they have to have to have different fieldid. If it doesn't have fieldid just leave it blank. Hopefully that makes sense. First time asking here. Thanks again.

DocumentsID Field ID   Values

  1          190       Jordan          
  1          191       Kobe
  1          192       Rose  
  2          191       Kobe

Expected Results

DocumentsID   Values 

   1       Jordan; Kobe; Rose
   2           Kobe

[A previous answer I have done](http://stackoverflow.com/a/10381975/1048425) does a bit more explaining than most on how concatenating rows to columns using `FOR XML` actually works. — GarethD, Oct 29 '14 at 08:05

score 1 · Answer 1 · edited May 23 '17 at 12:20

There's a same question here that you can refer.

I've modified the query used in the accepted answer just to fit in your example.

set nocount on;
declare @YourTable table (DocumentID int, FieldID int, [Values] varchar(50))

insert into @YourTable VALUES (1,190,'Jordan')
insert into @YourTable VALUES (1,191,'Kobe')
insert into @YourTable VALUES (1,192,'Rose')
insert into @YourTable VALUES (2,191,'Kobe')
set nocount off

SELECT DISTINCT
t1.DocumentID
    ,STUFF(
               (SELECT
                    ', ' + t2.[Values]
                    FROM @YourTable t2
                    WHERE t1.DocumentID=t2.DocumentID
                    ORDER BY t2.[Values]
                    FOR XML PATH(''), TYPE
               ).value('.','varchar(max)')
               ,1,2, ''
          ) AS [Values]
FROM @YourTable t1
GROUP BY t1.DocumentID,t1.[Values]

Slight note - `DISTINCT` is not necessary if you remove `t1.[values]` from the `GROUP BY` clause (where it is also not necessary). Making these corrections will make the query more efficient. — GarethD, Oct 29 '14 at 08:07

Concatenate Rows in SQL with Different values

1 Answers1