Dear All,
Thanks for your kind support. It was really not easy to write a script specially when requirement is not clear. I made a few changes in suggested queries and it gave me desired results.
With MyCTE
AS
(
select distinct T1.worker_id,AllRateCodes.SET_OF_CODES from #worker_rate T1
cross apply
(
SELECT SET_OF_CODES = STUFF((SELECT ',' + T2.rate_code
from #worker_rate T2
where T1.worker_id = T2.worker_id
ORDER BY rate_code
FOR XML PATH('')),1,1,'')
) AllRateCodes
)
SELECT
T1.WORKER_ID, T1.SET_OF_CODES, DENSE_RANK() OVER (ORDER BY T1.SET_OF_CODES) AS SNO FROM MYCTE T1 ORDER BY SET_OF_CODES
The required output was like this:
If worker 1 has rates a/b/c
then we have to find other workers who have rates a/b/c.
If worker 2 has a/b/c/d
It should not show with worker 1 as a/b/c and with other worker as a/b/c/d.
So worker_id will be distinct in the list.
worker_id set_of_codes sno
4 1,2 1
1 1,2,3 2
2 1,2,3 2
3 1,2,3 2