Grouping recordsets in sql

Question

Grouping is done from and toloc , and one group is indicated by usrid

 Table : from toloc usrid ab 1 cd 1 --- group 1 ef 1 ------------------- ab 2 cd 2 --- group 2 ef 2 ---------------------- ab 3 cd 3 --- group 3 hk 3 

after requesting a set of groups resulset is required ???

 from toloc usrid ab 1 cd 1 --- group 1 & 3 combined to form 1 group ef 1 ------------------- ab 2![alt text][1] cd 2 --- group 2 hk 2 

How can I achieve a set of results. I have to group a similar set of records in sql. Is it possible to do with rollup or new sets of groupings. I could not understand it.

Answer 1

I dug up this old question. Assuming there are no duplicate rows, it should work.

I decided the one you contacted and rewrote it to match this, 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 dh FROM d JOIN c ON c.COUNT = d.COUNT and c.h2 = dh 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 @tt WHERE NOT EXISTS (SELECT 1 FROM e WHERE eh = t.usr_history) 

Answer 2

The answer to this question is here: fooobar.com/questions/1318387 / ...

Another way is to use FOR XML PATH as the signature for the recordset.