SQL - Distinct on two columns and summarized values on third

Question

Hi!

I have a table with 3 different IDs. I want to distinct on column 1 and 2 and summarize the values of column 3 (into one field - maybe comma separated string). The summarized field doesn't have to "look nice" (no problems with: '4,3,' (comma at the end)). I'm using MS SQL Server 2008.

E.g:

ID1  ID2  ID3
 1    1    5
 1    1    8
 1    2    5
 1    2    8
 2    3   10
 2    3   11
 2    5   12

SELECT ...?

The result:

ID1   ID2   Summary
 1     1      5,8
 1     2      5,8
 2     3      10,11
 2     5      12

Answer 1

There are no built-in aggregates in MSSQL that can achieve that. This link shows multiple ways of concatenating string values based on a group.

Answer 2

Edit - removed trailing spaces in the third column

Since you said the summarized field doesn't have to look nice, try this:

SELECT M1.ID1, M1.ID2,
(
    SELECT convert(nvarchar(50), ID3) + ',' 
    FROM MyTable M2
    WHERE M1.ID1 = M2.ID1 AND M1.ID2 = M2.ID2
    FOR XML PATH('')
) M1
FROM MyTable M1
GROUP BY M1.ID1, M1.ID2

It assumes your table name is called 'MyTable'. This query results in commas at the end, but should get you started. Using my test database, this was the output:

ID1 ID2  M1
1   1   5,8,
1   2   5,8,
2   3   10,11,
2   5   12,

If you want it cleaned up, this should work (although the query itself is ugly):

SELECT ID1, ID2, left(M1, len(M1) - 1) AS M1
FROM
(
    SELECT M1.ID1, M1.ID2,
    (
        SELECT convert(nvarchar(50), ID3) + ',' 
        FROM MyTable M2
        WHERE M1.ID1 = M2.ID1 AND M1.ID2 = M2.ID2
        FOR XML PATH('')
    ) M1
    FROM MyTable M1
    GROUP BY M1.ID1, M1.ID2
) CleanedUp

Answer 3

I did that in Oracle but I think you can do the same in SQL Server

select id1, id2, testfunction(id1, id2)
from test 
group by  id1, id2;

The testfunction is defined as

create or replace function testfunction(v_id1 in number, v_id2 in number) return varchar2 is
  v_result varchar2(200);
  v_id3 number;

  cursor ids(p_id1 in number, p_id2 in number)  is 
   select id3 from test where id1 = p_id1 and id2 = p_id2;
begin
   open ids(v_id1, v_id2);
   loop
     fetch ids into v_id3;
     exit when ids%notfound;

     v_result := v_result || v_id3 || ',';
   end loop;
   close ids;

   return(v_result);
end testfunction;