looping join

[simanek]

Hi all,

Say I have a table with indices 0-9. I want to end up with a table that has the rows in the table all joined but in a unique matter. For instance, say i had the following:

TABLE 1:
0 val0
1 val1
2 val2
3 val3

I want to end up with:
0 val0 1 val1 2 val2 3 val3
1 val1 2 val2 3 val3
2 val2 3 val3
3 val3

I'm thinking there's a way to do something like:
#make a temp copy of TABLE1 and call it TABLE2
for(i=0; i<maxIndex; i++){
TABLE2 = join TABLE1, TABLE2 WHERE TABLE2.index>TABLE1.index
}

but obviously, this doesn't work since I don't know two thing: how to make a copy of a table, and how to join two tables and assign it to one of the tables that already exists. Any help would be GREATLY appreciated. Thanks. Mike
~~~~
simanek@uiuc.edu
&quot;It's a Swingline!&quot;
~~~~

 

[Kenrae]

You can allways do something like:

SELECT t1.*,t2.* FROM table1 t1, table1 t2
WHERE ...

(This will join a table with itself)
Or

CREATE TABLE table2 AS(SELECT * FROM table1)

(This will make a copy of table1)