Row and column conversion-oracle

Conversion of rows and columns, two methods for the same effect

Select t5. name, sum (t5.HTML) as HTML, sum (t5.JSP) as JSP, sum (t5. SQL) asSQL, sum (t5.Java) asJava, sum (t5. total) as total score, sum (t5. average score) as average score, sum (rownum) as total score from (

Select t0.zas name, sum (t0.a) as HTML, sum (t0. B) as JSP, sum (t0.c) asSQL, sum (t0.d) asJava, sum (t0.a) + sum (t0. B) + sum (t0.c) + sum (t0.d) as total score, (sum (t0.a) + sum (t0. B) + sum (t0.c) + sum (t0.d)/4as average from (

Selectt1.stu _ nameas z, t1.score as a, 0as B, 0as c, 0as d

From (

Selectstu. stu_name, s. score, sub. sub_namefrom SCORE s

Innerjoin student stu

On (stu. s_id = s. stu_id)

Innerjoin subject sub

On (sub. sub_id = s. sub_id)

Wheresub. sub_id = '5') t1

Union

Selectt2.stu _ nameas z, 0as a, t2.scoreas B, 0as c, 0as d from (

Selectstu. stu_name, s. score, sub. sub_namefrom SCORE s

Innerjoin student stu

On (stu. s_id = s. stu_id)

Innerjoin subject sub

On (sub. sub_id = s. sub_id)

Wheresub. sub_id = '6') t2

Union

Selectt3.stu _ nameas z, 0as a, 0as B, t3.score as c, 0as dfrom (

Selectstu. stu_name, s. score, sub. sub_namefrom SCORE s

Innerjoin student stu

On (stu. s_id = s. stu_id)

Innerjoin subject sub

On (sub. sub_id = s. sub_id)

Wheresub. sub_id = '7') t3

Union

Selectt4.stu _ nameas z, 0as a, 0as B, 0as c, t4.scoreas d from (

Selectstu. stu_name, s. score, sub. sub_namefrom SCORE s

Innerjoin student stu

On (stu. s_id = s. stu_id)

Innerjoin subject sub

On (sub. sub_id = s. sub_id)

Wheresub. sub_id = '8') t4

) T0

Groupby t0.z

Orderby total desc

) T5

Groupby t5. name

Orderby total score ranking;

Select tt2.asas, sum(tt2.html) as html, sum (tt2.jsp) as alias) + sum (tt2.jsp) + sum (tt2. SQL) + sum (tt2.java)/4as average score, sum (rownum) from (

Selecttt1.stu _ nameas name,

MAX (CASE tt1.kcWHEN 'html' THEN tt1.scELSE0END) as HTML,

MAX (CASE tt1.kcWHEN 'jsp 'THEN tt1.scELSE0END) as JSP,

MAX (CASE tt1.kcWHEN 'SQL' THEN tt1.scELSE0END) assql,

MAX (CASE tt1.kcWHEN 'java' THEN tt1.scELSE0END) asjava

From (

Selectstu. stu_name, s. scoreas SC, sub. sub_nameas kc from SCORE s

Innerjoin student stu

On (stu. s_id = s. stu_id)

Innerjoin subject sub

On (sub. sub_id = s. sub_id) tt1

Groupby tt1.stu _ name) tt2

Groupby tt2. name

Orderby total desc

;