concatenate rows and columns in sql server 2008

I have three columns FirstName,LastNAme,MiddleName

Table A

FirstNAme LastName MiddleName  ID
 James     Tony     peter       1
 Jack      Kallis   end         1
  nnnn      nnn      nn开发者_StackOverflown        2
  mmm       mm       aa         3

(there may be ten names with one id or one name with id )

Output for FullName where ID=1 should be:

FullName
James Tony Peter,Jack Kallis end

---

You can do this with the STUFF and FOR XML PATH method:

SELECT 
    STUFF(
       (SELECT
           ',' + ISNULL(FirstName, '') + ' ' + ISNULL(LastName, '') + ' ' + ISNULL(MiddleName, '')
        FROM
           dbo.TableA
        WHERE
           ID = 1
        FOR XML PATH('')), 1, 1, '')

This should rendered your desired output.