SQL SELF JOIN TO CONCATENATE COLUMNS

Question

Possible Duplicate:
Simulating group_concat MySQL function in MS SQL Server 2005?

I have two table namely ServiceEntryPart and Part. One service entry could have multiple parts. What I am trying to do is to concatenate different parts for the same service entry. The final entry I am looking for is to have something like below"

 ServiceEntryID     PartDescription
       3            2 ~ xyz Manager | 3 ~ Elevator

In the Column Part Description, different part ids are concatenated in one column by using part id first followed by a tilda and part description followed by a Pipe character and the same format for different parts in the serviceentry part. Any help would be appriciated. thanks

please find the structure below

dbo.ServiceEntryPart
ID   ServiceEntryID   PartID
266  2                1
234  3                2
234  3                3
233  5                4

dbo.Part
ID  PartDescription   
1   Sample Manager
2   xyz Manager
3   Elevator

The title reads different, but it's exactly what you want - using a group_concat-like thing in SQL server. — Marc B, Jul 19 '12 at 22:03

score 1 · Accepted Answer · answered Jul 19 '12 at 22:11

SELECT ServiceEntryID, PartDescription = 
    STUFF((SELECT ' | ' + CAST(b.ID AS NVARCHAR) + ' ~ ' + PartDescription
           FROM Part b
            INNER JOIN ServiceEntryPart c
                ON c.PartId = b.ID
           WHERE c.ServiceEntryID = a.ServiceEntryID
           FOR XML PATH('')), 1, 3, '')
FROM ServiceEntryPart a
GROUP BY ServiceEntryID

score 0 · Answer 2 · answered Jul 19 '12 at 22:04

0

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

answered Jul 19 '12 at 22:04

gloomy.penguin

5,833
6
33
59

Elwi · Answer 3 · 2012-07-19T22:20:59.053

In the next url you can find many methods to do this job, I recommend you to use The blackbox XML methods described in that tutorial, is the easiest way to do it.

Concatenating Row Values in Transact-SQL

Something Like this...

SELECT dbo.ServiceEntryPart.ServiceEntryID,
   ( SELECT dbo.Part.ID + '~' + dbo.Part.PartDescription + ','
       FROM dbo.Part
      WHERE dbo.ServiceEntryPart.PartID = dbo.Part.ID
      ORDER BY dbo.Part.ID
        FOR XML PATH('') ) AS PartDescription
  FROM dbo.ServiceEntryPart
  GROUP BY dbo.ServiceEntryPart.ServiceEntryID

SQL SELF JOIN TO CONCATENATE COLUMNS

3 Answers3