Q: getting literal values into row output

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 22 Aug 2002 15:12:36 -0800
Message-ID: <3d6561d4@news.victoria.tc.ca>

Sometimes I want to duplicate each row a number of times for a set of values, sometimes numeric, sometimes not. This is fine if the set of values is in a table

    create table set_of_values ( thing varchar2(10) );

    insert into things values 'CAR';
    insert into things values 'TRAIN';
    insert into things values 'BUS';

    ... etc ...

Now to repeat each item for every thing in the set

    select thing , item from set_of_values , items     

so if we have items abc and xyz then we get a display like

    CAR abc
    TRAIN abc

    BUS     abc
    CAR     xyz
    TRAIN   xyz
    BUS     xyz

Is there a simple way to do this with out two tables? In particular is there a way to include the set of values (i.e. CAR, TRAIN, BUS) into the output by including them literally in an SQL statement somehow?

e.g. (doesn't work of course)

    select thing , item

    from   items , (select thing from ('CAR','BUS','TRAIN') ) ;
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
                    in this example I want this to return 
                    three rows, one thing per row.

Received on Thu Aug 22 2002 - 18:12:36 CDT