grouping items in sql query

Question

I have a table with columns like (in sql server 2000)

MailCode   Mode   Name     Group
--------  -----  --------- -------
1          1      abc        0
1          1      def        0
1          1      qwe        1
2          2      aaw        0
2          2      aad        0

I want to group the Name field based on the rest of the fileds so that the result looks like this (there should be only one unique mailCode, Mode and group combination)

MailCode   Mode      Names            Group
---------   ------   ------------      -------
1           1        abc, def           0
1           1        qwe                1
2           2        aaw, aad           0

How can I create the sql query for this?

Answer 1

SQL Server 2000 solution

Luckily, COALESCE is supported in 2000, so you can use the COALESCE trick to create a comma delimited list of values, demonstrated in this link. Because of the variable usage, you'll need to create a function/procedure and call it within the main query. Basically, just replace the STUFF() in the query below with the function call.

SQL Server 2005+ solution:

  SELECT x.mailcode,
         x.mode,
         STUFF((SELECT y.name
                  FROM TABLE y
                 WHERE y.mailcode = x.mailcode
                   AND y.mode = x.mode
                   AND y.gropu = x.group
              GROUP BY y.mailcode, y.mode, y.group
               FOR XML PATH(', ')), 1, 1, '') AS name,
         x.group
    FROM TABLE x
GROUP BY x.mailcode, x.mode, x.group

Answer 2

I can't think of a simple query that will get the result you're looking for, but some logic along these lines should get you where you want:

1) Loop through distinct MailCode, Mode, Group Rows
  A) select all names in group
   A.1) Loop through names
   A.2) Concatenate them together into temp variable
  B) insert all data (MailCode, Mode, Group, temp variable) into temp table

Fair waring, looping in SQL tends to have a huge performance hit when it comes to large datasets. I unfortunately don't know a better way to do it.

Answer 3

I had a similar problem where I had to concatenate a field in the select, my solution at the time was to create a procedure that returned the result and called it like this

select x as field1, y as field2, dbo.procedure as field3