Guest david gill Posted February 24, 2009 Share Posted February 24, 2009 Hello everyone, I have a problem, i'm trying to pair up all registered houses in the same postcode from one table. So like in one table i've got like 6 different postcodes and i need to pair them up. The answer should be like this. Example: HNo PC HNo PC ____________________________________ 1 LE1 2hg | 1 LE1 2hg 1 LE1 2hg | 2 LE1 2hg 1 LE1 2hg | 3 LE1 2hgetc......HNO = house numberPC = Post codeI've tried the cartesian product but it gives me a return of 1640 rows? :S Can someone help me or give me a clue as to which statement or command i should use Link to comment Share on other sites More sharing options...
Stefano Posted March 24, 2009 Share Posted March 24, 2009 select ahno, apc, bhno, bpcfrom( select a.hno as ahno, a.pc as apc, b.hno as bhno, b.pc as bpc from ( select rowno, hno, pc from yourtable) as a, ( select rowno, hno, pc from yourtable) as b where a.rowno mod 2 = 0 and b.rown mod 2 = 1 and (a.rowno-1)=b.rowno) I haven't debuged it let me know if helpsregardsStefano De Boni Link to comment Share on other sites More sharing options...
Stefano Posted March 24, 2009 Share Posted March 24, 2009 select ahno, apc, bhno, bpcfrom( select a.hno as ahno, a.pc as apc, b.hno as bhno, b.pc as bpc from ( select rowno, hno, pc from yourtable) as a, ( select rowno, hno, pc from yourtable) as b where a.rowno mod 2 = 0 and b.rown mod 2 = 1 and (a.rowno-1)=b.rowno) I haven't debuged it let me know if helpsregardsStefano De Boni Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.