row-by-row table join?
December 24, 2009 - 9:50 pm
I have two tables of equal length that I want to join together row by row:
Table 1
Number|
———–|
1 |
2 |
3 |
______|
Table 2
Letter|
——–|
a |
b |
c |
——-|
result
Letter | Number |
———|————–|
a | 1 |
etc.
A JOIN statement in the query would require something to JOIN ON to prevent getting all combinations, right? (a,1; a,2; a,3; b,1;etc). The only thing I can think of is to imbed a sequence generation inline and join on that:
SELECT Letter, i++ AS count1 from Table 1 JOIN Number, j++ AS count2 FROM Table2 ON count1=count2;
However, I don’t think PosgreSQQL(or maybe even any database) can do something like than, can they?
Hmmmm.
December 25th, 2009 at 3:07 am
Aside from the fact that this looks like terrible design, and there will be all sorts of potential pitfalls (are your letters and numbers ordered, etc.) here’s one answer that will work in Sql Server 2005/2008. I’m not a PostgreSQL user, so not sure if this can translate.
CREATE TABLE Numbers(Number int NOT NULL)
INSERT Numbers ( Number ) VALUES (1)
INSERT Numbers ( Number ) VALUES (2)
INSERT Numbers ( Number ) VALUES (3)
CREATE TABLE Letters(Letter varchar (50) NOT NULL)
INSERT Letters ( Letter ) VALUES (’a')
INSERT Letters ( Letter ) VALUES (’b')
INSERT Letters ( Letter ) VALUES (’c')
SELECT Let.Letter + CAST(Num.Number AS VARCHAR(MAX))
FROM
(SELECT Number, ROW_NUMBER() OVER(ORDER BY Number) AS JoinId
FROM Numbers) Num
JOIN
(SELECT Letter, ROW_NUMBER() OVER(ORDER BY Letter) AS JoinId
FROM Letters) Let
ON Num.JoinId = Let.JoinId
References :
December 25th, 2009 at 3:19 am
Hmmmm.
References :