Re: How do u insert a virtual node into a table

Try this...

SELECT TO_CHAR(station) col1, 'LINE_' || TO_CHAR(station) col2, station FROM train_stops
UNION
SELECT 'LINE_' || TO_CHAR(station), food_stand, station FROM train_stops
ORDER BY station;

The only reason I included the station was for the purpose of ordering the results. I'm sure there are other ways (perhaps better), but here's at least one solution.

SQL> SELECT * FROM train_stops;

LINE     STATION FOOD_STAND     PROFIT
-----        ---------- ----------                ----------
SS530                  1  D300                              132
SS530                  1  D301-1                            23
SS530                  1  D302-1                            75
SS530                  2  D305                               54
SS530                  2  D306                             243
SS530                  2  D307                             129

COL1       COL2          STATION
----------  ----------     ----------
1                LINE_1                   1
LINE_1     D300                       1
LINE_1     D301-1                   1
LINE_1     D302-1                   1
2                LINE_2                   2
LINE_2     D305                       2
LINE_2     D306                       2
LINE_2     D307                       2

"julio" <julio33_at_whomail.com> wrote in message news:3d10fe39$0$64316$45beb828_at_newscene.com...
>
> using 8.1.6 on sun server with Sun OS 2.7
>
> We have the following data which provides a train line, a station and a
food
> stand
>
> line station food_stand profit
> SS530 1 D300 132
> SS530 1 D301-1 23
> SS530 1 D302-1 75
> SS530 2 D305 54
> SS530 2 D306 243
> SS530 2 D307 129
>
> we want to be able to roll up to the station and line level. To do so we
> want to map the relationship between the station and food_stand as such
>
> 1 D300
> 1 D301-1
> 1 D302-1
> 2 D305
> 2 D306
> 2 D307
>
> that is doable, but what we want to also do is create a 'node' between
> stations and food_stands so that a station is connected to a node and each
> stand at that station is connected to that node as such
>
> 1 node_1
> node_1 D301-1
> node_1 D302-1
> node_1 D305
> 2 node_2
> node_2 D306
> node_2 D307
>
>
> Questions:
>
> Is this doable via SQL without pl/sql?
>
> If not some ideas for pl/sql psuedo code would be greatly appreciated
>
> Julio and Colin and Lyse
>
> See my babies at
>
> http://www.geocities.com/colin_and_lyse/colin_lyse.html
Received on Thu Jun 20 2002 - 01:24:46 CDT