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!

Transposing data in oracle

Discussion in 'SQL PL/SQL' started by laxminewtooracle, Jun 28, 2016.

  1. laxminewtooracle

    laxminewtooracle Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    USA
    Hi all,

    I have a table (TABLE1):

    CREATE TABLE TABLE1
    (
    STDNO NUMBER(10),
    SUBJ VARCHAR2(5),
    MARKS NUMBER(10)
    );

    with this data in it:

    INSERT INTO TABLE1 VALUES (1, 'A', 50);
    INSERT INTO TABLE1 VALUES (1, 'B', 100);
    INSERT INTO TABLE1 VALUES (1, 'C', 25);
    INSERT INTO TABLE1 VALUES (1, 'D', 50);
    INSERT INTO TABLE1 VALUES (1, 'E', 100);
    INSERT INTO TABLE1 VALUES (1, 'F', 20);
    INSERT INTO TABLE1 VALUES (1, 'G', 40);
    INSERT INTO TABLE1 VALUES (1, 'H', 60);
    INSERT INTO TABLE1 VALUES (1, 'I', 80);
    INSERT INTO TABLE1 VALUES (1, 'J', 100);
    INSERT INTO TABLE1 VALUES (1, 'K', 100);

    I have another table (TABLE2):

    CREATE TABLE TABLE2
    (
    STDNO NUMBER(10),
    SUBJ1 VARCHAR2(5),
    MARKS1 NUMBER(10),
    SUBJ2 VARCHAR2(5),
    MARKS2 NUMBER(10),
    SUBJ3 VARCHAR2(5),
    MARKS3 NUMBER(10),
    SUBJ4 VARCHAR2(5),
    MARKS4 NUMBER(10),
    SUBJ5 VARCHAR2(5),
    MARKS5 NUMBER(10)
    );

    I am looking for transposing the data from TABLE1 and TABLE2.
    And the final result of TABLE2 have to looks like:

    STDNO SUBJ1 MARKS1 SUBJ2 MARKS2 SUBJ3 MARKS3 SUBJ4 MARKS4 SUBJ5 MARKS5
    ---------- ----- ---------- ----- ---------- ----- ---------- ----- ---------- ----- ----------
    1 A 50 B 100
    1 C 25 D 50 E 100
    1 F 20 G 40 H 60 I 80 J 100
    1 K 100

    In my example, i need to transpose the data into new column whenever the MARKS field has the value as '100'.

    If is there any procedure or function to do this would be great, as in real environment the number of rows in TABLE1 will be unknown.

    Thanks in advance.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Since this must be homework what have you written to attempt to solve this problem? We won't do your assignment for you, but we will help you get it working.

    Post your efforts and we'll do our best to help.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    It is possible to use the method Start of Group method

    for example :
    Code (SQL):

    WITH t1 AS
    (
      SELECT t.*,
      ROW_NUMBER() OVER(ORDER BY t.stdno,t.subj,marks) rn
      FROM table1 t
    )
    ,preres AS
    (
    SELECT
      stdno,subj,marks,grp,rn1
    FROM t1
    model
    partition BY (stdno)
    dimension BY (rn)
    measures (subj,marks,1 grp,1 rn1)
    rules(

    grp[any] = CASE WHEN marks[cv()-1] = 100 THEN grp[cv()-1] + 1 ELSE nvl(grp[cv()-1],1) END ,
    rn1[any] = CASE WHEN marks[cv()-1] = 100 THEN 1 ELSE nvl(rn1[cv()-1],0) +1 END
    )
    )
    SELECT
      grp,
      MAX(CASE WHEN rn1 = 1 THEN subj  END) subj_1,
      MAX(CASE WHEN rn1 = 1 THEN marks END) marks_1,  
      MAX(CASE WHEN rn1 = 2 THEN subj  END) subj_2,
      MAX(CASE WHEN rn1 = 2 THEN marks END) marks_2,  
      MAX(CASE WHEN rn1 = 3 THEN subj  END) subj_3,
      MAX(CASE WHEN rn1 = 3 THEN marks END) marks_3,  
      MAX(CASE WHEN rn1 = 4 THEN subj  END) subj_4,
      MAX(CASE WHEN rn1 = 4 THEN marks END) marks_4,  
      MAX(CASE WHEN rn1 = 5 THEN subj  END) subj_5,
      MAX(CASE WHEN rn1 = 5 THEN marks END) marks_5
    FROM
      preres p
    GROUP BY p.grp
    ;
     
    Last edited: Jul 1, 2016