CODEWITHSUNDEEP
sqloracle-databaseview
Joel Seah
Joel Seah

Reputation: 714

How to display row data into different column ORACLE

I'm new to SQL and currently this is what I'm trying to do:
Display multiple rows of data into different columns within the same row

I have a table like this:

CREATE TABLE TRIPLEG(
    T#              NUMBER(10)      NOT NULL,
    LEG#            NUMBER(2)       NOT NULL,
    DEPARTURE       VARCHAR(30)     NOT NULL,
    DESTINATION     VARCHAR(30)     NOT NULL,
    CONSTRAINT TRIPLEG_PKEY PRIMARY KEY (T#, LEG#),
    CONSTRAINT TRIPLEG_UNIQUE UNIQUE(T#, DEPARTURE, DESTINATION),
    CONSTRAINT TRIPLEG_FKEY1 FOREIGN KEY (T#) REFERENCES TRIP(T#) );
INSERT INTO TRIPLEG VALUES( 1, 1, 'Sydney', 'Melbourne');
INSERT INTO TRIPLEG VALUES( 1, 2, 'Melbourne', 'Adelaide');

The result should be something like this:

T# | ORIGIN | DESTINATION1 | DESTINATION2
1 | SYDNEY | MELBORUNE | ADELAIDE

The origin is the DEPARTURE.
DESTINATION1 could either be DEPARTURE OR DESTINATION.
DESTINATION2 is the DESTINATION.

The query should include the COUNT(T#) < 3 since I only need to display the records less than 3. How can i do this with 2 relational view to achieve this result?

Upvotes: 0

Views: 3944

Answers (3)

Somasundaram V
Somasundaram V

Reputation: 11

Kindly try this

select t#,regexp_substr(tree,'[^:]+',2) origin,
regexp_substr(tree,'[^:]+',2,2) destination1,
regexp_substr(tree,'[^:]+',2,3) destination2
from 
(
    select a.*, 
        sys_connect_by_path(departure,':') tree, 
        level lvl 
    from tripleg a
        connect by nocycle prior destination = departure and prior t# = t#
        start with leg# = 1
        and level < = 3
)
where lvl = 3;

Upvotes: 1

Srini V
Srini V

Reputation: 11355

You can make use of hierarchical queries.

Data Setup:

CREATE TABLE TRIPLEG ( T#   NUMBER ( 10 ) NOT NULL,
                   LEG# NUMBER ( 2 ) NOT NULL,
                   DEPARTURE VARCHAR ( 30 ) NOT NULL,
                   DESTINATION VARCHAR ( 30 ) NOT NULL );

INSERT INTO
      TRIPLEG
VALUES
      ( 1,
        1,
        'Sydney',
        'Melbourne' );

INSERT INTO
      TRIPLEG
VALUES
      ( 1,
        2,
        'Melbourne',
        'Adelaide' );

INSERT INTO
      TRIPLEG
VALUES
      ( 2,
        1,
        'A',
        'B' );

INSERT INTO
      TRIPLEG
VALUES
      ( 2,
        2,
        'B',
        'C' );

INSERT INTO
      TRIPLEG
VALUES
      ( 2,
        3,
        'C',
        'D' );

INSERT INTO
      TRIPLEG
VALUES
      ( 3,
        1,
        'A',
        'B' );

COMMIT;

Query:

SELECT
      T#,
      CONNECT_BY_ROOT DEPARTURE
      || SYS_CONNECT_BY_PATH ( DESTINATION,
                          ' ~ ' )
          AS ROUTE
FROM
      TRIPLEG
WHERE
      LEVEL = 2
CONNECT BY
      NOCYCLE DEPARTURE = PRIOR DESTINATION
START WITH
      T# = '1';

Explanation:

  1. Connect rows based on the relationship between Departure and Destination.
  2. Filter for rows satisfying a transit (Strictly)
  3. LEG# is not used.

Upvotes: 1

Klas Lindb&#228;ck
Klas Lindb&#228;ck

Reputation: 33273

Try this:

select t1.T#, 
       (select t2.departure from tripleg t2 where t1.T# = t2.T# and t2.LEG# = 1) Origin,
       (select t2.destination from tripleg t2 where t1.T# = t2.T# and t2.LEG# = 1) Destination1,
       (select t2.destination from tripleg t2 where t1.T# = t2.T# and t2.LEG# = 2) Destination2
  from tripleg t1
 group by t1.T#
having count(1) < 3

Upvotes: 1

Related Questions