Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Laxmi.

Asked: June 28, 2016 - 3:37 pm UTC

Last updated: July 01, 2016 - 2:23 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi Tom,

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.

and Chris said...

This is very similar to the answer I just gave!

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9530067900346161176

The key difference is how to calculate the new groups. This finds the first value form the current row down:

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

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);

select *
from
  (select stdno, subj, marks,
    dense_rank ( ) over ( order by fv ) g, 
    row_number ( ) over ( partition by fv order by subj ) rn
  from
    (select s.*, 
      first_value ( t ) ignore nulls over ( 
        order by subj rows between current row and unbounded following
      ) fv
    from
      (select stdno, subj, marks,
        case
          when marks = 100 then rownum
        end t
      from table1
      ) s
    ) s
  ) pivot ( min ( subj ) as subj, min ( marks ) as marks 
    for ( rn ) in ( 1 as "1", 2 as "2", 3 as "3" ) )
order by g;

     STDNO          G 1 1_MARKS 2 2_MARKS 3 3_MARKS
---------- ---------- - ------- - ------- - -------
         1          1 A      50 B     100          
         1          2 C      25 D      50 E     100
         1          3 F      20 G      40 H      60
         1          4 K     100

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Laxmi, June 30, 2016 - 6:52 pm UTC

Thank you so much Tom, it is very useful and i need to enhance the code according to my data.
Chris Saxon
July 01, 2016 - 2:23 am UTC

Glad we could help

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.