Concatenate many rows into a single text string with grouping

Question

I have the following table: tblFile

tblFile

My Desired output:

enter image description here

I am Concatenating many rows into a single text string; however, I cannot get the grouping correct. As the code is now it will just display for each record in the FileNameString field: AAA,BBB,CCC,DDD,EEE,FFF

Any suggestions with the grouping!

SELECT FileID, Stuff(
(SELECT     N', ' + CONVERT(Varchar, FileName) 
FROM         tblFile  FOR XML PATH(''),TYPE )
.value('text()[1]','nvarchar(max)'),1,2,N'')AS FileNameString 
From tblFile
GROUP BY FileID

Jasmina Shevchenko · Accepted Answer · 2013-08-21T16:24:50.450

20

try this -

SELECT DISTINCT
      fileid
    , STUFF((
        SELECT N', ' + CAST([filename] AS VARCHAR(255))
        FROM tblFile f2
        WHERE f1.fileid = f2.fileid ---- string with grouping by fileid
        FOR XML PATH (''), TYPE), 1, 2, '') AS FileNameString
FROM tblFile f1

edited Aug 21 '13 at 16:24

answered Aug 21 '13 at 16:18

Jasmina Shevchenko

797
6
17

Replacing first char with nothing is incorrect way – Obsivus Jun 15 '15 at 13:54
14

I received an Error: Argument data type xml is invalid for argument 1 of stuff function .. – Kris Khairallah Feb 27 '16 at 08:33
5

Remove the TYPE parameter for a working version. TYPE causes the result to be returns as an XML data type, but STUFF needs a string. – gxclarke Oct 06 '16 at 15:30

Concatenate many rows into a single text string with grouping

1 Answers1

Linked