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