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

Discussion in 'SQL PL/SQL' started by bnramesh8, Apr 19, 2016.

  1. bnramesh8

    bnramesh8 Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi All,
    I have table like below
    T1:
    col1 col2 col3
    1 aa 300
    2 bb 200
    3 cc 100
    i want out put as
    col1 col2 col3
    1 aa 100
    1 aa 100
    1 aa 100
    2 bb 100
    2 bb 100
    3 cc 100

    can any one please tell me how can i achieve using oracle sql query..

    Thanks in advance...
    Ramesh
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation

    Code (SQL):

    WITH tab (col1,col2,col3) AS
    (
      SELECT 1, 'aa', 300 FROM dual UNION ALL
      SELECT 2, 'bb', 200 FROM dual UNION ALL
      SELECT 3, 'cc', 100 FROM dual
    )
    SELECT
      t.col1,
      t.col2,
      t.col3,
      100 n_col3
    FROM tab t
    CONNECT BY level <= t.col3/100
    AND
    prior  sys_guid () IS NOT NULL
    AND
    col1 = prior col1
    AND
    col2 = prior col2;

    SQL>
      COL1 COL2  COL3  N_COL3
    ---------- ---- ---------- ----------
      1 aa  300  100
      1 aa  300  100
      1 aa  300  100
      2 bb  200  100
      2 bb  200  100
      3 cc  100  100
    6 ROWS selected

     

    Are you need to distribute the sum multiply of other value?
     
    Last edited: Apr 19, 2016
    bnramesh8 likes this.
  3. bnramesh8

    bnramesh8 Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Thanks for your valuable feedback.... Krasnslobodtsev..