Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

number the Group on data from two columns duplicated Expand / Collapse
Author
Message
Posted Thursday, January 14, 2010 11:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 15, 2010 9:59 AM
Points: 3, Visits: 15
Hi All,

i have a table LinkTable which contains two intger columns ID1 and ID2 which may contain duplicate data interchanged in different rows
Sample data of the table,

SID(primary key)| ID1 | ID2
=================================
1 | 101 | 102
2 | 102 | 101
3 | 103 | 104
4 | 103 | 105
5 | 104 | 105
6 | 106 | 104
7 | 107 | 108

I need a sql query which will form groups based on values of ID1 and ID2 and need to number the group starting from 1

ID1 | ID2
================================================
101 | 102 => 1 and 2 will go to group 1
102 | 101 => Already 1 and 2 is added so ignore

103 | 104 => 3 and 4 will make group 2
103 | 105 => 5 will go to group 2 as its ID1 is 3, which in turn is part of group 1
104 | 105 => ignore since 4 and 5 already added
106 | 104 => 6 will go to group 2 as its ID2 4 is of group 2

107 | 108 => 7 and 8 will make group 3

so the result will look like this :
Group | Member
=======|============
1 | 101
1 | 102

2 | 103
2 | 104
2 | 105
2 | 106

3 | 107
3 | 108

Please help me to solve this problem,

Thanks
Sh.
Post #847767
Posted Thursday, January 14, 2010 12:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 6,824, Visits: 13,274
Would you mind sharing the business case that would use such a strange data structure?

Regarding your requirement:
Am I right that you'd need to start a new group as soon as neither one of the numbers in one row has been previously assigned to a group?
If so, how would you handle row 6 to be "6 | 101 | 104" instead of "6 | 106 | 104" (both values in a row are preassigned, but to different groups)?
Are the groups supposed to be consecutive or can you "jump back" to let's say group 1 if row 8 would be 8 | 101 | 109?

Again: really strange requirement...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #847841
Posted Thursday, January 14, 2010 1:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 15, 2010 9:59 AM
Points: 3, Visits: 15
Hi Lutz,

Thanks for the quick reply,
Regarding the requirement ,

In LinkTable , ID1 contains the uniqueid of a transaction and ID2 contans the uniqueid of the transaction related to it,
One transaction can be related to 0 or many transactions,

We are in the process of creating specific groups for all related transaction.

Quote: Am I right that you'd need to start a new group as soon as neither one of the numbers in one row has been previously assigned to a group?

Ans: Yes, you are correct ,

Quote:If so, how would you handle row 6 to be "6 | 101 | 104" instead of "6 | 106 | 104" (both values in a row are preassigned, but to different groups)?
Are the groups supposed to be consecutive or can you "jump back" to let's say group 1 if row 8 would be 8 | 101 | 109?

Ans :
If the linktable contains data :
SID(primary key)|ID1 |ID2
====================
1| 101 | 102
2| 102 | 101
3| 103 | 104
4| 103 | 105
5| 104 | 105
6| 101 | 104
7| 107 | 108
8| 101 | 109

The expected output is:

GroupNum| ID
==========
1| 101
1| 102
1| 103
1| 104
1| 105
2| 107
2| 108
1| 109

Thanks & Regards
Sh.
Post #847907
Posted Thursday, January 14, 2010 4:39 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 6,824, Visits: 13,274
I thought about applying the concept of a hierarchical CTE (aka recursive CTE, see BOL for details). Basically, I would have used the smaller ID per row as "manager" and the other number as "employee" (this would actually take care of (101,102) and (102,101) not to cause an infinite loop.
But after spending quite some time with it, I have to give up.
The sample data you provided do not show any consistent logical pattern to apply a hierarchy structure to.
Examples:
In your second set of sample "employee" 105 would have to report to "manager" 104 and both have to report to 103 at the same time.

As far as I can see, the only way to solve that puzzle would be using a *cough* cursor *cough**cough* since it would handle one row at a time, ignoring inconsistent data. (Un)fortunately, my SQL Server version is not capable of programming a cursor (or maybe I'm not. Can't tell.)

But the much more recommended way would be to get normalized data. Those could be transformed using hierarchy tools like recursive CTEs.
Btw: I can't think of a business case using transaction IDs that are related in a many to many relationship including circular reference across several levels...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #848024
Posted Thursday, January 14, 2010 8:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667
shaini.binu (1/14/2010)
Hi Lutz,

Thanks for the quick reply,
Regarding the requirement ,

In LinkTable , ID1 contains the uniqueid of a transaction and ID2 contans the uniqueid of the transaction related to it,
One transaction can be related to 0 or many transactions,

We are in the process of creating specific groups for all related transaction.

Quote: Am I right that you'd need to start a new group as soon as neither one of the numbers in one row has been previously assigned to a group?

Ans: Yes, you are correct ,

Quote:If so, how would you handle row 6 to be "6 | 101 | 104" instead of "6 | 106 | 104" (both values in a row are preassigned, but to different groups)?
Are the groups supposed to be consecutive or can you "jump back" to let's say group 1 if row 8 would be 8 | 101 | 109?

Ans :
If the linktable contains data :
SID(primary key)|ID1 |ID2
====================
1| 101 | 102
2| 102 | 101
3| 103 | 104
4| 103 | 105
5| 104 | 105
6| 101 | 104
7| 107 | 108
8| 101 | 109

The expected output is:

GroupNum| ID
==========
1| 101
1| 102
1| 103
1| 104
1| 105
2| 107
2| 108
1| 109

Thanks & Regards
Sh.

Then why doesn't 103, 104, and 105 live in a group 2 by themselves?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #848064
Posted Friday, January 15, 2010 12:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 6,824, Visits: 13,274
Jeff Moden (1/14/2010)
shaini.binu (1/14/2010)

Quote: Am I right that you'd need to start a new group as soon as neither one of the numbers in one row has been previously assigned to a group?

Ans: Yes, you are correct ,


Ans :
If the linktable contains data :
SID(primary key)|ID1 |ID2
====================
1| 101 | 102
2| 102 | 101
3| 103 | 104
4| 103 | 105
5| 104 | 105
6| 101 | 104
7| 107 | 108
8| 101 | 109

The expected output is:

GroupNum| ID
==========
1| 101
1| 102
1| 103
1| 104
1| 105
2| 107
2| 108
1| 109

Thanks & Regards
Sh.

Then why doesn't 103, 104, and 105 live in a group 2 by themselves?

As far as I would read it, row #6 makes 103 to 105 belonging to group 1.
Instead of "previously assigned" the term probably should be "assigned anywhere in the table".
I think to solve this puzzle you'd need to order the rows asc by the value of either id1 or id2 (per row), depending on which one is less than the other...
Using the example from above you'd end up with
SID(primary key)|ID1 |ID2
====================
1| 101 | 102
2| 101 | 102 (ID1 and ID2 switched)
6| 101 | 104
8| 101 | 109
3| 103 | 104
4| 103 | 105
5| 104 | 105
7| 107 | 108

... at least that's what I guess should be done to get the expected result...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #848105
Posted Friday, January 15, 2010 9:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 15, 2010 9:59 AM
Points: 3, Visits: 15
Hi,
I have solved the puzzle with the help of cursurs, Thanks for all the help,

Thanks
Sh.
Post #848350
Posted Friday, January 15, 2010 7:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667
shaini.binu (1/15/2010)
Hi,
I have solved the puzzle with the help of cursurs, Thanks for all the help,

Thanks
Sh.


Two way street here, shaini... please post the code for your solution.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #848669
Posted Tuesday, March 2, 2010 3:05 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
Would I be correct in thinking that these are reconciliation groups?

If you have 3 receipts and 10 invoices with cross associations in the reconciliation they all need to be in the same reconciliation group....

not sure exactly how to solve the problem yet, but cursors is NOT a nice solution.

Which version on MSSQL are you on.

Post #875565
Posted Tuesday, March 2, 2010 6:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667
Jeff Moden (1/15/2010)
shaini.binu (1/15/2010)
Hi,
I have solved the puzzle with the help of cursurs, Thanks for all the help,

Thanks
Sh.


Two way street here, shaini... please post the code for your solution.


Heh... so much for Shaini... Stuck with cursors and doesn't even know he has a problem... yet.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #875614
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse