The grouping is done on from and toloc and one group is been indicated by usrid
Table :
from toloc usrid
a b 1
c d 1 --- group 1
e f 1
-------------------
a b 2
c d 2 --- group 2
e f 2
----------------------
a b 3
c d 3 --- group 3
h k 3
after group set query required resulset ???
from toloc usrid
a b 1
c d 1 --- group 1 & 3 combined to form 1 group
e f 1
-------------------
a b 2![alt text][1]
c d 2 --- group 2
h k 2
How can I achieve the resultset.
I have to group similar set of records in sql. Is it possible to do with rollup or the new grouping sets. I'm not been able to figure it.
I dug up this old question. Assuming there there are no duplicated rows it should work.
I solved the one you linked to first and rewrote it to match this one, so the fields will differ in names compared to your example.
DECLARE @t TABLE (fromloc VARCHAR(30), toloc VARCHAR(30), usr_history INT)
INSERT @t VALUES ('a', 'b', 1)
INSERT @t VALUES ('c', 'b', 1)
INSERT @t VALUES ('e', 'f', 1)
INSERT @t VALUES ('a', 'b', 2)
INSERT @t VALUES ('c', 'b', 2)
INSERT @t VALUES ('e', 'f', 2)
INSERT @t VALUES ('a', 'b', 3)
INSERT @t VALUES ('c', 'd', 3)
INSERT @t VALUES ('h', 'k', 3)
;WITH c as
(
SELECT t1.usr_history h1, t2.usr_history h2, COUNT(*) COUNT
FROM @t t1
JOIN @t t2 ON t1.fromloc = t2.fromloc and t1.toloc = t2.toloc and t1.usr_history < t2.usr_history
GROUP BY t1.usr_history, t2.usr_history
),
d as (
SELECT usr_history h, COUNT(*) COUNT FROM @t GROUP BY usr_history
),
e as (
SELECT d.h FROM d JOIN c ON c.COUNT = d.COUNT and c.h2 = d.h
JOIN d d2 ON d2.COUNT=c.COUNT and d2.h= c.h1
)
SELECT fromloc, toloc, DENSE_RANK() OVER (ORDER BY usr_history) AS 'usrid'
FROM @t t
WHERE NOT EXISTS (SELECT 1 FROM e WHERE e.h = t.usr_history)
Answer to this question is here: https://stackoverflow.com/a/6727662/195446
Another way is to use FOR XML PATH as signature of set of records.
