How to transform SQL column into rows  

  RSS

Demon
(@demonguru)
Member Admin
Joined: 5 years  ago
Posts: 41
25/04/2018 2:29 pm  

TABLE

CREATE TABLE col_to_rows(stu_name VARCHAR2(30),

subject VARCHAR2(10),marks NUMBER);

INSERT INTO col_to_rows VALUES('GEORGE','ECO',77);

INSERT INTO col_to_rows VALUES('GEORGE','HIS',99);

INSERT INTO col_to_rows VALUES('GEORGE','MAT',64);

INSERT INTO col_to_rows VALUES('GEORGE','GEO',85);

INSERT INTO col_to_rows VALUES('GEORGE','SCI',98);

INSERT INTO col_to_rows VALUES('ROBERT','ECO',71);

INSERT INTO col_to_rows VALUES('ROBERT','HIS',90);

INSERT INTO col_to_rows VALUES('ROBERT','MAT',84);

INSERT INTO col_to_rows VALUES('ROBERT','GEO',95);

INSERT INTO col_to_rows VALUES('ROBERT','SCI',58);

INSERT INTO col_to_rows VALUES('TIMOTHY','ECO',56);

INSERT INTO col_to_rows VALUES('TIMOTHY','HIS',55);

INSERT INTO col_to_rows VALUES('TIMOTHY','MAT',67);

INSERT INTO col_to_rows VALUES('TIMOTHY','GEO',54);

INSERT INTO col_to_rows VALUES('TIMOTHY','SCI',45);

COMMIT;


Answer 1:

SELECT stu_name,

max(CASE WHEN subject='ECO' THEN marks ELSE 0 END) ECO,

max(CASE WHEN subject='HIS' THEN marks ELSE 0 END) HIS,

max(CASE WHEN subject='MAT' THEN marks ELSE 0 END) MAT,

max(CASE WHEN subject='GEO' THEN marks ELSE 0 END) GEO,

max(CASE WHEN subject='SCI' THEN marks ELSE 0 END) SCI

FROM col_to_rows

GROUP BY stu_name


Answer 2:

SELECT stu_name,

MAX(decode(subject,'ECO',marks,0)) ECO,

MAX(decode(subject,'HIS',marks,0)) HIS,

MAX(decode(subject,'MAT',marks,0)) MAT,

MAX(decode(subject,'GEO',marks,0)) GEO,

MAX(decode(subject,'SCI',marks,0)) SCI

FROM col_to_rows GROUP BY stu_name


Answer 3:

SELECT stu_name,

max(CASE WHEN rn=1 THEN marks ELSE 0 END) ECO,

max(CASE WHEN rn=2 THEN marks ELSE 0 END) GEO,

max(CASE WHEN rn=3 THEN marks ELSE 0 END) HIS,

max(CASE WHEN rn=4 THEN marks ELSE 0 END) MAT,

max(CASE WHEN rn=5 THEN marks ELSE 0 END) SCI FROM

(SELECT stu_name,subject,marks, rank() over (PARTITION BY stu_name ORDER BY subject )rn FROM col_to_rows)

GROUP BY stu_name

DemonGuru18


ReplyQuote
Share: