MySQL Oracle行列互转

Mysql

行转列

建表

CREATE TABLE `student` (
	`id` INT ( 10 ) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR ( 20 ) DEFAULT NULL,
	`course` VARCHAR ( 20 ) DEFAULT NULL,
	`score` FLOAT DEFAULT '0',
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

测试数据

INSERT INTO student ( NAME, course, score )
VALUES
	( "张三", "数学", 34 ),
	( "张三", "语文", 58 ),
	( "张三", "英语", 58 ),
	( "李四", "数学", 45 ),
	( "李四", "语文", 87 ),
	( "李四", "英语", 45 ),
	( "王五", "数学", 76 ),
	( "王五", "语文", 34 ),
	( "王五", "英语", 89 );

行转列

SELECT name AS 姓名,
	max( CASE course WHEN '数学' THEN score ELSE 0 END ) AS 数学,
	max( CASE course WHEN '语文' THEN score ELSE 0 END ) AS 语文,
	max( CASE course WHEN '英语' THEN score ELSE 0 END ) AS 英语 
FROM
	student 
GROUP BY name;

列转行

建表

CREATE TABLE `grade` (
	`id` INT ( 10 ) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR ( 20 ) DEFAULT NULL,
	`cn_score` FLOAT DEFAULT NULL,
	`math_score` FLOAT DEFAULT NULL,
	`en_score` FLOAT DEFAULT '0',
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

测试数据

INSERT INTO grade ( name, cn_score, math_score, en_score )
VALUES
	( "张三", 34, 58, 58 ),
	( "李四", 45, 87, 45 ),
	( "王五", 76, 34, 89 );

列转行

SELECT name,'语文' AS course,cn_score AS score FROM grade 
UNION
SELECT name,'数学' AS course,math_score AS score FROM grade 
UNION
SELECT name,'英语' AS course,en_score AS score FROM grade;

Oracle

在Oracle 11g中增加了2个查询：pivot（行转列） 和unpivot（列转行）

列转行(pivot)

CREATE TABLE student
(
    id     NUMBER(11) NOT NULL primary key,
    name   NVARCHAR2(20),
    course NVARCHAR2(20),
    score  NUMBER
)

INSERT INTO student (id, name, course, score) VALUES ('1', '张三', '数学', '34');
INSERT INTO student (id, name, course, score) VALUES ('2', '张三', '语文', '58');
INSERT INTO student (id, name, course, score) VALUES ('3', '张三', '英语', '58');
INSERT INTO student (id, name, course, score) VALUES ('4', '李四', '数学', '45');
INSERT INTO student (id, name, course, score) VALUES ('5', '李四', '语文', '87');
INSERT INTO student (id, name, course, score) VALUES ('6', '李四', '英语', '45');
INSERT INTO student (id, name, course, score) VALUES ('7', '王五', '数学', '76');
INSERT INTO student (id, name, course, score) VALUES ('8', '王五', '语文', '34');
INSERT INTO student (id, name, course, score) VALUES ('9', '王五', '英语', '89');

行转列

SELECT
	name AS 姓名,
	max( math_score ) AS 数学,
	max( cn_score ) AS 语文,
	max( en_score ) AS 英语 
FROM
	student PIVOT ( SUM( score ) FOR course IN ( '数学' AS math_score, '语文' AS cn_score, '英语' AS en_score ) ) 
GROUP BY
	name

注意： pivot（聚合函数 for 列名 in（类型）） ，其中 in(‘’) 中可以指定别名，in中还可以指定子查询，比如 SELECT DISTINCT course FROM student

行转列(unpivot)

create table grade
(
   id         NUMBER(11) not null
        primary key,
    name       NVARCHAR2(20),
    cn_score   NUMBER,
    math_score NUMBER,
    en_score   NUMBER
)

INSERT INTO grade (id, name, cn_score, math_score, en_score) VALUES ('1', '张三', '34', '58', '58');
INSERT INTO grade (id, name, cn_score, math_score, en_score) VALUES ('2', '李四', '45', '87', '45');
INSERT INTO grade (id, name, cn_score, math_score, en_score) VALUES ('3', '王五', '76', '34', '89');

列转行

SELECT name,
       course,
       score
FROM
    grade unpivot ( score FOR course IN ( math_score as '数学', cn_score as '语文', en_score as '英语') )

注意： unpivot没有聚合函数，course、score字段也是临时的变量

Oracle、MySQL实现行列互转