Oracle row-column conversion, oracle row-column Conversion

Oracle row-column conversion, oracle row-column Conversion
I. Table creation and data insertion

1.1 create a table

1 create table kecheng 2 (3 id NUMBER, 4 name VARCHAR2 (20), 5 course VARCHAR2 (20), 6 score NUMBER 7); 8 insert into kecheng (id, name, course, score) 9 values (1, 'zhang san', 'China', 67); 10 insert into kecheng (id, name, course, score) 11 values (1, 'zhang san', 'mat', 76); 12 insert into kecheng (id, name, course, score) 13 values (1, 'zhang san', 'English ', 43 ); 14 insert into kecheng (id, name, course, score) 15 values (1, 'zhang san', 'hire', 56); 16 insert into kecheng (id, name, course, score) 17 values (1, 'zhang san', 'chemistry ', 11); 18 insert into kecheng (id, name, course, score) 19 values (2, 'Li si ', 'China', 54); 20 insert into kecheng (id, name, course, score) 21 values (2, 'lily', 'mat', 81 ); 22 insert into kecheng (id, name, course, score) 23 values (2, 'lily', 'inc', 64); 24 insert into kecheng (id, name, course, score) 25 values (2, 'Lee 4', 'hire', 93); 26 insert into kecheng (id, name, course, score) 27 values (2, 'Lee 4 ', 'chemistry ', 27); 28 insert into kecheng (id, name, course, score) 29 values (3, 'wang 5', 'China', 24 ); 30 insert into kecheng (id, name, course, score) 31 values (3, '王', 'mat', 25); 32 insert into kecheng (id, name, course, score) 33 values (3, 'wang wu', 'English ', 8); 34 insert into kecheng (id, name, course, score) 35 values (3, 'wang wu ', 'hire', 45); 36 insert into kecheng (id, name, course, score) 37 values (3, '王', 'Chemical', 1); 38 commit;

2. Fixed row-column Conversion

2.1 Decode method

 

Select id, NAME, SUM (DECODE (course, 'China', score, 0) language, -- max and min can be used here to SUM (DECODE (course, 'mat ', score, 0) mathematics, SUM (DECODE (course, 'English ', score, 0) English, SUM (DECODE (course, 'hire', score, 0 )) history, SUM (DECODE (course, 'chemistry ', score, 0) Chemistry FROM kechengGROUP by id, NAME

 

 

 

2.2 Case Method

 

Select id, NAME, MAX (case when course = 'China' THEN score ELSE 0 END) language, MAX (case when course = 'mate' THEN score ELSE 0 END) mathematics, MAX (case when course = 'English 'THEN score ELSE 0 END) English, MAX (case when course = 'hired' THEN score ELSE 0 END) history, MAX (case when course = 'Chemical' THEN score ELSE 0 END) Chemistry FROM kechengGROUP by id, NAME

 

The results are the same as those above.

2.3. wmsys. wm_concat row and column Conversion Function

 

SELECT ID,NAME,wmsys.wm_concat(course || ':'||score) courseFROM kechengGROUP BY ID ,NAME;

 

 

2.4 use over (partition by t. u_id)

 

SELECT NAME,wmsys.wm_concat(course ||score)  OVER (PARTITION BY NAME)FROM kecheng

 

 

 

Iii. Dynamic Conversion

3.1 Use PL/SQL

DECLARE -- store the final SQL LV_ SQL VARCHAR2 (3000); -- store the connected SQL SQL _COMMOND VARCHAR2 (3000); -- Define the CURSOR CUR IS SELECT COURSE FROM KECHENG GROUP BY COURSE; BEGIN -- Define SQL _COMMOND: = 'select name'; for I in cur loop -- connect the result to SQL _COMMOND: = SQL _COMMOND | ', SUM (DECODE (course, ''' | I. COURSE | ''', score, 0) '| I. COURSE; DBMS_OUTPUT.PUT_LINE (SQL _COMMOND); end loop; SQL _COMMOND: = SQL _COMMOND | 'from KECHENG group by name'; LV_ SQL: = 'insert INTO temp_ss' | SQL _COMMOND; DBMS_OUTPUT.PUT_LINE (LV_ SQL); execute immediate LV_ SQL; END;

Temp_ss table