行转列
在sqlserver 数据库中提供 PIVOT用于将列值旋转为列名(即行转列),在SQL Server 2000可以用聚合函数配合CASE语句实现
PIVOT的一般语法是:PIVOT(聚合函数(列) FOR 列 in (…) )AS P
完整语法:
table_source
PIVOT(
聚合函数(value_column)
FOR pivot_column
IN(<column_list>)
)
UNPIVOT用于将列明转为列值(即列转行),在SQL Server 2000可以用UNION来实现
完整语法:
table_source
UNPIVOT(
value_column
FOR pivot_column
IN(<column_list>)
)
创建表
CREATE TABLE `t_score` (
`id` int(32) NOT NULL,
`name` varchar(10) NOT NULL,
`course` varchar(10) DEFAULT NULL,
`score` int(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `t_score`
-- ----------------------------
BEGIN;
INSERT INTO `t_score` VALUES ('1', '张三', '语文', '80'), ('2', '张三', '数学', '85'), ('3', '张三', '英语', '90'), ('4', '李四', '语文', '88'), ('5', '李四', '数学', '91'), ('6', '李四', '英语', '79');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
法一:
SELECT DISTINCT a.name,
(SELECT score FROM t_score b WHERE a.name=b.name AND b.course='语文')AS'语文',
(SELECT score FROM t_score b WHERE a.name=b.name AND b.course='数学')AS'数学',
(SELECT score FROM t_score b WHERE a.name=b.name AND b.course='英语')AS'英语'
FROM t_score a
法二:
SELECT name,
SUM(IF (course = '语文' , score , null ) ) as '语文',
SUM(IF (course = '数学' , score , null ) ) as '数学',
SUM(IF (course = '英语' , score , null ) ) as '英语 '
FROM t_score GROUP BY name
或
SELECT name,
SUM(IF (course = '语文' , score , null ) ) as '语文',
SUM(IF (course = '数学' , score , null ) ) as '数学',
SUM(IF (course = '英语' , score , null ) ) as '英语 '
FROM t_score GROUP BY name
法三:
存储过程:
mysql版
BEGIN
#课程名称
DECLARE course_n VARCHAR(20);
#所有课程数量
DECLARE count INT;
#计数器
DECLARE i INT DEFAULT 0;
#拼接SQL字符串
SET @s = 'SELECT name';
SET count = (SELECT COUNT(distinct course) FROM t_score);
WHILE i < count DO
SET course_n = (SELECT course FROM t_score LIMIT i,1);
SET @s = CONCAT(@s, ', SUM(CASE course WHEN ','\'', course_n,'\'',' THEN score END )',' AS ','\'',course_n,'\'');
SET i = i+1;
END WHILE;
SET @s = CONCAT(@s, ' FROM t_score GROUP BY name');
#用于调试
#SELECT @s;
PREPARE stmt FROM @s;
EXECUTE stmt;
END