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

Occasionally need to understand, study this article, reprint record

From: http://blog.csdn.net/jxzkin/article/details/7949629

1. Create test data

[HTML]? View Plain Copy

1. CREATE? TABLE? Cc??
2. ?? (Student?) NVARCHAR2 (2), Course? NVARCHAR2 (2), Score?int??
3. ??);??

[HTML]? View Plain Copy

2. Insert?into? Cc???
4. select? N ' Zhang San ', n ' language ', 78?from?dual?union?all??
6. select? N ' Zhang San ', n ' math ', 87?from?dual?union?all??
8. select? N ' Zhang San ', n ' English ', 82?from?dual?union?all??
10. select? N ' Zhang San ', n ' physics ', 90?from?dual?union?all??
12. select? n ' John Doe ', n ' language ', 65?from?dual?union?all??
14. select? n ' John Doe ', n ' math ', 77?from?dual?union?all??
16. select? n ' John Doe ', n ' English ', 65?from?dual?union?all??
18. select? n ' John Doe ', n ' physics ', 85?from?dual?;??
20. commit;??

want to see the results of the query :?

[HTML]? View Plain Copy

1. John Doe ? 77?85?65?65?292??
2. Zhang San ? 87?90?82?78?337 ??
   2. using the wm_concat method

[HTML]? View Plain Copy

1. SELECT? Student,wm_concat (Score), SUM (score)? From? Cc? GROUP? By? STUDENT;??

3. using the Oracle 11g pivot method

[HTML]? View Plain Copy

2. SELECT? Kin.*,??
4. ?? Kin.a+kin.b+kin.c+kin.d?as? Total??
6. From??
8. ?? (SELECT??????????????????????????????? *??
10. ?? From? Cc? PIVOT? (? MAX (score)? For? COURSE? In? (' language '? As? A?,? ' Mathematics '? As? B,? ' English '? As? C, ' physical '? As? D)??
12. ??)? KIN;??

4. using the DECODE method

[HTML]? View Plain Copy

2. SELECT??
4. Student,??
6. MAX (Decode (COURSE,? ' ) language ',? Score))? A??
8. MAX (DECODE (COURSE,? ' ) Mathematics ',? Score))? B??
10. MAX (DECODE (COURSE,? ' ) English ',? Score))? C??
12. MAX (DECODE (COURSE,? ' ) Physical ',? Score))? D??
14. SUM (score)? Total??
16. From??
18. Cc??
20. GROUP? By??
22. Student;??

Such a problem, to find out his key points. In fact, it is a row to the column, this is a classmate in the itpub problem.

Solution to the problem:

Build table:

CREATE TABLE T_result

(d varchar2 (Ten), result Varchar2 (4));

Insert data:

INSERT into T_result values (' 2014-01-01 ', ' wins ');

INSERT into T_result values (' 2014-01-01 ', ' wins ');

INSERT into T_result values (' 2014-01-01 ', ' negative ');

INSERT into T_result values (' 2014-01-02 ', ' wins ');

INSERT into T_result values (' 2014-01-02 ', ' negative ');

INSERT into T_result values (' 2014-01-02 ', ' negative ');

?

To scan the table two times

Select T1.d,t1.c1 ' wins ', t2.c2 ' negative ' from

(select count (Result) c1,d from t_result where result = ' wins ' GROUP by D) T1

Left OUTER JOIN

(select count (Result) c2,d from t_result where result = ' negative ' GROUP by D) T2

On t1.d = t2.d

Row to column:

SELECT D,sum (Decode (result, ' wins ', 1,0)), SUM (decode (result, ' negative ', 1,0))

From T_result

GROUP by D

Or

Select D,

SUM (case result when ' wins ' then 1 else 0 end) wins,

SUM (case result when ' negative ' then 1 else 0 end) negative

From T_result GROUP by D order by D;

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