Self join challenge
-
I have the following problem to solve and I do not want to use cursors:
Table A:
Col1 Col2
1 3
1 4
1 5
2 6
2 7
2 8
I want to create a self joined table:
Table B
Col1 Col2_1 Col2_2
1 3 4
1 4 5
2 6 7
2 7 8
This is like a parent-child relationship query. Any ideas?
Michael
-
How about this?
select t1.col1 as COL1, t1.col2 as COL2_1, t2.col2 as COL2_2
FROM tbltable t1, tbltable t2
WHERE t1.col1=t2.col1
AND t1.col2<t2.col2
AND t1.col2<>(t2.col2)-2
Ryan
-
Ryan, it was a nice try but would not solve my problem.
My example was a bit misleading: the numbers in Col2 do not nicely increment by 1, but by any arbitary number.
I tried the following: first create two temp tables then join them. But it looks a bit clumsy. I wonder if there is method to generate the esquential numbers (where I used Identity) without resorting to temp tables.
select id = IDENTITY(int, 1,1), a.Col1, a.Col2 into #a
from table a join table b on a.Col1 = b.Col1 and a.Col2 < b.Col2
group by a.Col1, a.Col2
order by a.Col1, a.Col2
select id = IDENTITY(int, 1,1), a.Col1, a.Col2 into #a
from table a join table b on a.Col1 = b.Col1 and a.Col2 < b.Col2
group by a.Col1, a.Col2
order by a.Col1, a.Col2
select #a.*, #b.*
from #a join #b on #a.id = #b.id
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply