开发者

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;

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜