Oracle row-to-column (pivot, WM_CONCAT, decode) Usage Summary

CREATE TABLE CC
(Student NVARCHAR2 (2), Course NVARCHAR2 (2), score INT
);

INSERT into CC
Select n ' Zhang San ', N ' language ', dual union ALL
Select N ' Zhang San ', N ' math ', the dual union ALL
SE Lect N ' Zhang San ', N ' English ', from dual UNION ALL
Select N ' Zhang San ', N ' Physics ', all-from-dual UNION ALL
Select n ' John Doe ', n ' language ', from dual UNION ALL
Select n ' John Doe ', n ' math ', dual from the union ALL
Select n ' John Doe ', n ' English ', all in dual union ALL
Select n ' John Doe ', N ' objects The dual;
Commit;

/*
--Want to see results:
Li 477 292
Zhang 387 337 1 decode Method
SELECT
Student, Br>max (DECODE (COURSE, ' language ', score)) A,
Max (DECODE (COURSE, ' math ', score)) B,
MAX (DECODE (COURSE, ' English ', score)) C,
max (DECODE (COURSE, ' physical ', score)) D,
SUM (Score) Total
from
CC
GROUP by
Student;

--2 using the Wm_concat method
SELECT Student,wm_concat (Score), SUM (score) from the CC GROUP by STUDENT;
--3 using the Oracle 11g Pivot method
SELECT kin.*, Kin.a + kin.b + kin.c + KIN.D as Total
From (SELECT *
From CC PIVOT (MAX (score) to COURSE in (' Language ' as A,
' Math ' as B,
' English ' as C,
' Physical ' as D ')) KIN;

Transferred from: http://blog.csdn.net/jxzkin/article/details/7949629

Oracle row-to-column (pivot, WM_CONCAT, decode) Usage Summary