SQL - Distinct on two columns and summarized values on third
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
开发者_StackOverflow1 1 5,8
1 2 5,8
2 3 10,11
2 5 12
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
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.
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;
精彩评论