Sql 2005 Merge / Concatenate Multiple Rows To One Column

We have a bit of a SQL quandry. Say I have a results that look like this... 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | A 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | B 61E77D90-D53D-4E2E-

Solution 1:

try this:

set nocount on;
declare@ttable (id char(36), x char(1))
insertinto@t (id, x)
select'61E77D90-D53D-4E2E-A09E-9D6F012EB59C' , 'A'unionselect'61E77D90-D53D-4E2E-A09E-9D6F012EB59C' , 'B'unionselect'61E77D90-D53D-4E2E-A09E-9D6F012EB59C' , 'C'unionselect'61E77D90-D53D-4E2E-A09E-9D6F012EB59C' , 'D'unionselect'7ce953ca-a55b-4c55-a52c-9d6f012ea903' , 'E'unionselect'7ce953ca-a55b-4c55-a52c-9d6f012ea903' , 'F'set nocount off

SELECT p1.id, 
          stuff(
                   (SELECT' '+ x
                        FROM@t p2
                        WHERE p2.id=p1.id
                        ORDERBY id, x
                        FOR XML PATH('') 
                   )
                   ,1,1, ''
               ) AS YourValues
      FROM@t p1
      GROUPBY id

OUTPUT:

id                                   YourValues
------------------------------------ --------------
61E77D90-D53D-4E2E-A09E-9D6F012EB59C AB C D
7ce953ca-a55b-4c55-a52c-9d6f012ea903 E F

(2 row(s) affected)

EDIT based on OP's comment about this needing to run for an existing query, try this:

;WITH YourBugQuery AS
(
    --replace this with your own queryselect'61E77D90-D53D-4E2E-A09E-9D6F012EB59C'AS ColID , 'A'AS ColX 
    unionselect'61E77D90-D53D-4E2E-A09E-9D6F012EB59C' , 'B'unionselect'61E77D90-D53D-4E2E-A09E-9D6F012EB59C' , 'C'unionselect'61E77D90-D53D-4E2E-A09E-9D6F012EB59C' , 'D'unionselect'7ce953ca-a55b-4c55-a52c-9d6f012ea903' , 'E'unionselect'7ce953ca-a55b-4c55-a52c-9d6f012ea903' , 'F'

)
SELECT p1.ColID, 
          stuff(
                   (SELECT' '+ ColX
                        FROM YourBugQuery p2
                        WHERE p2.ColID=p1.ColID
                        ORDERBY ColID, ColX
                        FOR XML PATH('') 
                   )
                   ,1,1, ''
               ) AS YourValues
      FROM YourBugQuery p1
      GROUPBY ColID

this has the same results set as displayed above.

Solution 2:

I prefer to define a custom user-defined aggregate. Here's an example of a UDA which will accomplish something very close to what you're asking.

Why use a user-defined aggregate instead of a nested SELECT? It's all about performance, and what you are willing to put up with. For a small amount of elements, you can most certainly get away with a nested SELECT, but for large "n", you'll notice that the query plan essentially runs the nested SELECT once for every row in the output list. This can be the kiss of death if you're talking about a large number of rows. With a UDA, it's possible to aggregate these values in a single pass.

The tradeoff, of course, is that the UDA requires you to use the CLR to deploy it, and that's something not a lot of people do often. In Oracle, this particular situation is a bit nicer as you can use PL/SQL directly to create your user-defined aggregate, but I digress...

Solution 3:

Another way of doing it is to use the FOR XML PATH option

SELECT
    [ID],
    (
        SELECT
            [Value] + ' 'FROM
            [YourTable] [YourTable2]
        WHERE
            [YourTable2].[ID] = [YourTable].[ID]
        ORDERBY
            [Value]
        FOR XML PATH('')
    ) [Values]
FROM
    [YourTable]
GROUPBY
    [YourTable].[ID]