T-SQL How to concatenate string field of first n rows in group by statement

Question

I have a table as below:

FileHash              FilePath
1                     A.txt
3                     e.txt
3                     f.txt
3                     g.txt
3                     h.txt
2                     B.txt
2                     c.txt

In T-SQL, How to concatenate the first 3 FilePath of each FileHash as follows:

FileHash              FilePath
1                     A.txt
2                     B.txt, C.txt
3                     e.txt, f.txt, g.txt

Do some research before asking a question :) – Pரதீப் Dec 25 '14 at 11:45 — Pரதீப், Dec 25 '14 at 11:45

score 1 · Answer 1 · edited Dec 25 '14 at 11:55

1

One method:

WITH files
     AS (SELECT DISTINCT FileHash
         FROM   dbo.test)
SELECT FileHash,
       Stuff((SELECT ', ' + FilePath
              FROM   dbo.Test AS fp
              WHERE  fp.FileHash = files.FileHash
              FOR XML PATH('')), 1, 2, '') AS FilePath
FROM   files;

edited Dec 25 '14 at 11:55

Pரதீப்

88,697
17
124
160

answered Dec 25 '14 at 11:52

Dan Guzman

38,909
3
38
62

How did you answer after the question has been marked as **duplicate**. I don't see any answering options or answer text box – Pரதீப் Dec 25 '14 at 11:58
Could be a concurrency bug. I should add that the referenced thread is 5 years old and protected. It doesn't include the technique using a CTE with a correlated subquery, which is most appropriate here. – Dan Guzman Dec 25 '14 at 12:04
I didn't say anything wrong about the answer:) was wondering how you answered after question been marked as duplicate. – Pரதீப் Dec 25 '14 at 12:08
@NoDisplayName http://meta.stackexchange.com/a/91928/145673 – Martin Smith Dec 25 '14 at 12:37
@MartinSmith - Got it :) – Pரதீப் Dec 25 '14 at 12:46

T-SQL How to concatenate string field of first n rows in group by statement

1 Answers1