数据库之行转列与列转行方法

--创建纵表TABLE_A

CREATE TABLE TABLE_A (     NAME VARCHAR2(20),     SUBJECT VARCHAR2(20),     SCORE NUMBER(22,0));

--向表TABLE_A中插入数据

INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('张三', '语文', 60); INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('张三', '数学', 70); INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('张三', '英语', 80); INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('李四', '语文', 90); INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES ('李四', '数学', 100);

插入数据后TABLE_A如下图：

--创建纵表TABLE_B

CREATE TABLE TABLE_B (     NAME VARCHAR2(20),     CHINESE NUMBER(22,0),     MATH NUMBER(22,0),     ENGLISH NUMBER(22,0));

--向表TABLE_B中插入数据

INSERT INTO TABLE_B (NAME, CHINESE, MATH, ENGLISH) VALUES ('张三', 60, 70, 80); INSERT INTO TABLE_B (NAME, CHINESE, MATH, ENGLISH) VALUES ('李四', 90, 100, 0);

插入数据后 TABLE_B如下图：

--纵表转横表 TABLE_A-->TABLE_B

--方法一：使用case ... when ... then ... else ... end  函数

select name,         sum (case subject when '语文' then score else 0 end) as chinese,         sum (case subject when '数学' then score else 0 end) as math,         sum (case subject when '英语' then score else 0 end) as english         from Table_A         group by name

--方法二：使用pivot

select * from Table_A pivot (max(score)for subject in('语文','数学','英语'))

--横表转纵表 TABLE_B-->TABLE_A

--方法一：使用union all

select NAME,'语文' as SUBject,chinese as score from Table_B union all select NAME,'数学' as SUBject,math as score from Table_B union all select NAME,'英语' as SUBject,english as score from Table_B order by name,subject desc

--方法二：使用unpivot

select name,subject,score from Table_B unpivot (score for subject in (chinese,math,english))