Jump to content

Pairing Up Data In One Table.


Guest david gill
 Share

Recommended Posts

Guest david gill

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

  • 4 weeks later...
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

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...