Jump to content

Pairing Up Data In One Table.


Guest david gill

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...