1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Sql query required

Discussion in 'SQL PL/SQL' started by karthikeyanc2003, Oct 7, 2011.

  1. karthikeyanc2003

    karthikeyanc2003 Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    100
    Hi Guys,

    I have a table test as below

    Select * from test ;

    COL 1 COL 2 COL 3
    ----------------------
    John A 1
    John B 2
    John C 3
    John D 4
    John E 5
    Scott W 1
    Scott X 2
    Scott Y 3
    Scott Z 4



    I need output as follows. I was able to generate F1 and F2 column But I have not able to genetrate F3 (Continous no generation for each COL 2 ).


    F1 F2 F3
    -------------------
    A B 1
    A C 2
    A D 3
    A E 4
    B A 1
    B C 2
    B D 3
    B E 4
    .
    .
    .
    .
    W X 1
    W Y 2
    W Z 3
    X W 1
    X Y 2
    X Z 3
    .
    .
    .

    Let me know in case if we can achive this by SQL rather than PLSQL . Since I already Achive the same in PL/SQL using cursor.

    Thanks In advance
    Karthi
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
    SQL> SELECT t1.col2 f1, t2.col2 f2, ROW_NUMBER() OVER (partition BY t1.col2 ORDER BY t1.col2, t2.col2) f3
      2  FROM test t1, test t2
      3  WHERE t1.col2 <> t2.col2
      4  AND t1.col1 = t2.col1
      5  /
    F F         F3
    - - ----------
    A B          1
    A C          2
    A D          3
    A E          4
    B A          1
    B C          2
    B D          3
    B E          4
    C A          1
    C B          2
    C D          3
    F F         F3
    - - ----------
    C E          4
    D A          1
    D B          2
    D C          3
    D E          4
    E A          1
    E B          2
    E C          3
    E D          4
    W X          1
    W Y          2
    F F         F3
    - - ----------
    W Z          3
    X W          1
    X Y          2
    X Z          3
    Y W          1
    Y X          2
    Y Z          3
    Z W          1
    Z X          2
    Z Y          3
    32 ROWS selected.
    SQL>
     
  3. expora

    expora Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    80
    Great David