Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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