SQL 2005 Merge / concatenate multiple rows to one column

Question

Hi,

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-A09E-9D6F012EB59C | C
61E77D90-D53D-4E2E-A09E-9D6F012EB59C | D
7ce953ca-a55b-4c55-a52c-9d6f012ea903 | E
7ce953ca-a55b-4c55-a52c-9d6f012ea903 | F

is there a way I can group these results within SQL to return as

61E77D90-D53D-4E2E-A09E-9D6F012EB59C | A B C D
7ce953ca-a55b-4c55-a52c-9d6f012ea903 | E F

Any ideas people?

Many thanks

Dave

Answer 1

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.

Answer 2

try this:

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

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

OUTPUT:

id                                   YourValues
------------------------------------ --------------
61E77D90-D53D-4E2E-A09E-9D6F012EB59C A B 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 query
    select '61E77D90-D53D-4E2E-A09E-9D6F012EB59C' AS ColID , 'A' AS ColX 
    union select '61E77D90-D53D-4E2E-A09E-9D6F012EB59C' , 'B' 
    union select '61E77D90-D53D-4E2E-A09E-9D6F012EB59C' , 'C' 
    union select '61E77D90-D53D-4E2E-A09E-9D6F012EB59C' , 'D' 
    union select '7ce953ca-a55b-4c55-a52c-9d6f012ea903' , 'E' 
    union select '7ce953ca-a55b-4c55-a52c-9d6f012ea903' , 'F'

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

this has the same results set as displayed above.

Answer 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]
        ORDER BY
            [Value]
        FOR XML PATH('')
    ) [Values]
FROM
    [YourTable]
GROUP BY
    [YourTable].[ID]