Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 number the Group on data from two columns duplicated Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, January 14, 2010 11:07 AM
 Forum 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 rowsSample 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 | 1011 | 102 2 | 1032 | 1042 | 1052 | 106 3 | 1073 | 108 Please help me to solve this problem,ThanksSh.
Post #847767
 Posted Thursday, January 14, 2010 12:42 PM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, February 10, 2016 11:50 AM Points: 6,897, Visits: 13,559
 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... LutzA pessimist is an optimist with experience. How to get fast answers to your question How to post performance related questionsLinks for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #847841
 Posted Thursday, January 14, 2010 1:41 PM
 Forum 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 | 109The expected output is:GroupNum| ID========== 1| 101 1| 102 1| 103 1| 104 1| 105 2| 107 2| 108 1| 109Thanks & RegardsSh.
Post #847907
 Posted Thursday, January 14, 2010 4:39 PM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, February 10, 2016 11:50 AM Points: 6,897, Visits: 13,559
 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... LutzA pessimist is an optimist with experience. How to get fast answers to your question How to post performance related questionsLinks for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #848024
 Posted Thursday, January 14, 2010 8:45 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 2:41 PM Points: 42,081, Visits: 39,473
 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 | 109The expected output is:GroupNum| ID========== 1| 101 1| 102 1| 103 1| 104 1| 105 2| 107 2| 108 1| 109Thanks & RegardsSh.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." Helpful Links:How to post code problemsHow to post performance problems
Post #848064
 Posted Friday, January 15, 2010 12:32 AM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, February 10, 2016 11:50 AM Points: 6,897, Visits: 13,559
 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 | 109The expected output is:GroupNum| ID========== 1| 101 1| 102 1| 103 1| 104 1| 105 2| 107 2| 108 1| 109Thanks & RegardsSh.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 | 1093| 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... LutzA pessimist is an optimist with experience. How to get fast answers to your question How to post performance related questionsLinks for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #848105
 Posted Friday, January 15, 2010 9:05 AM
 Forum 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,ThanksSh.
Post #848350
 Posted Friday, January 15, 2010 7:59 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 2:41 PM Points: 42,081, Visits: 39,473
 shaini.binu (1/15/2010)Hi, I have solved the puzzle with the help of cursurs, Thanks for all the help,ThanksSh.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." Helpful Links:How to post code problemsHow to post performance problems
Post #848669
 Posted Tuesday, March 2, 2010 3:05 PM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Monday, November 14, 2016 3:09 PM Points: 558, Visits: 879
 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-Forever Group: General Forum Members Last Login: Yesterday @ 2:41 PM Points: 42,081, Visits: 39,473
 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,ThanksSh.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." Helpful Links:How to post code problemsHow to post performance problems
Post #875614

 Permissions