CODEWITHSUNDEEP
sql-serversql-server-2008
deep
deep

Reputation: 31

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      nnn        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

Upvotes: 1

Views: 2528

Answers (1)

marc_s
marc_s

Reputation: 754628

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.

Upvotes: 1

Related Questions