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 post
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 post
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 post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...