I do not get the right result with the CHECKSUM suggestion. May be I am doing something wrong.

This is the result I am getting:

worker_id SNO

4 1

1 2

2 2

3 2

This is the result from the XML approach:

worker_id Codezworker_id Codez

1 1,2,32 1,2,3

1 1,2,33 1,2,3

2 1,2,33 1,2,3

The nice thing about the XML approach is that you get also the list of rate codes. IMHO it would be nice having an ordered set function for string aggregation (http://connect.microsoft.com/SQLServer/feedback/details/728969/feature-request-ordered-set-functions-within-group-clause).

There is another solution I learned from Peter (Peso) which yield a much better performance. Here is a nice article comparing three different approaches.

http://social.technet.microsoft.com/wiki/contents/articles/22165.t-sql-relational-division.aspx

WITH C1 AS (

SELECT

worker_id,

COUNT(*) AS cnt,

MIN(rate_code) AS min_rc,

MAX(rate_code) AS max_rc

FROM

#worker_rate

GROUP BY

worker_id

)

, C2 AS (

SELECT

B.worker_id,

B.rate_code,

A.cnt,

A.min_rc,

A.max_rc

FROM

C1 AS A

INNER JOIN

#worker_rate AS B

ON A.worker_id = B.worker_id

)

SELECT

A.worker_id AS lwid,

B.worker_id AS rwid

FROM

C2 AS A

INNER JOIN

C2 AS B

ON A.worker_id < B.worker_id

AND A.rate_code = B.rate_code

AND A.cnt = B.cnt

AND A.min_rc = B.min_rc

AND A.max_rc = B.max_rc

GROUP BY

A.worker_id,

B.worker_id

HAVING

COUNT(*) = MIN(B.cnt);

GO