Join values in a field

Question

I have a problem.

I need to join the values of a column in one field, separated by semicolons. I use the XML Path, but the response time is very long.

This is my table:

ID   COLOR
1    WHITE
1    RED
2    BLACK
2    RED

This is what I need to output:

ID   COLORS
1    WHITE; RED
2    BLACK; RED

How can I do it?

Really, my code is this:

SELECT *
FROM 
Client A
OUTER APPLY (SELECT Origen  + '; '
         FROM ( SELECT DISTINCT F.IdSC, C.Origen
            FROM SC F
                LEFT JOIN OrigenSC B ON F.IdSC = B.IdSC
            LEFT JOIN Origen   C ON B.IdOrigen = C.IdOrigen                             
        ) D
    WHERE D.Id = A.IdSC
    ORDER BY D.Origen
    FOR XML PATH('')) H (Origen)

This is a duplicate of this question: http://stackoverflow.com/questions/122942/how-to-return-multiple-values-in-one-column-t-sql — Ryan Rinaldi, Dec 12 '11 at 15:16
@Oded This is not a pivot query. It is simulating `GROUP_CONCAT` `@Longha` Show your slow XML PATH query. Also what indexes do you have on the table? — Martin Smith, Dec 12 '11 at 15:17
@MartinSmith The Indexes are the Clustered Index. In Client (Id), in SC is IdSC, in OrigenSC is IdSC and IdOrigen and in Origen is IdOrigen. — Longha, Dec 12 '11 at 15:37
Are there always two colors per ID or can there be more? If two colors, very simple solution. If more, you need the GROUP_CONCAT Martin is referring to — Sparky, Dec 12 '11 at 15:47
@Sparky The Colors is only example. The numbers of values is variable. — Longha, Dec 12 '11 at 15:55
possible duplicate of [Transposing a SQL Server 2008 table](http://stackoverflow.com/questions/8471555/transposing-a-sql-server-2008-table) — gbn, Dec 12 '11 at 16:12

score 0 · Answer 1 · answered Dec 12 '11 at 21:00

Given the COLORS example above, here is something you could try:

create table Colors
(
    ID INT,
    COLOR varchar(32)
)

INSERT INTO Colors values(1, 'WHITE')
INSERT INTO Colors values(1, 'RED')
INSERT INTO Colors values(2, 'BLACK')
INSERT INTO Colors values(2, 'RED')
INSERT INTO Colors values(3, 'GREEN')
INSERT INTO Colors values(3, 'RED')

WITH UniqueColors AS ( SELECT DISTINCT ID FROM Colors )
SELECT 
    ID,
    STUFF(
            (   SELECT '; '+COLOR 
                FROM Colors 
                WHERE ID = UniqueColors.ID 
                FOR XML PATH('') 
            ) 
            , 1, 2, ''
        ) AS [ColorList]
FROM UniqueColors

This was taken from some other example on SO.

Join values in a field

1 Answers1