日记 · 日常札记

数据库行转列

困惑已久的行转列的问题恍然大悟，主要问题出现在对case的理解不够透彻，今天想明白了记录下来：

简单例子：

将如下格式的表

id sid course grade

1 1 语文 75

2 1 数学 56

3 1 英语 88

4 2 语文 78

5 2 数学 98

6 2 英语 77

转换成

sid 语文数学英语

1 75 56 88

2 78 98 77

sqlserver建表初始化数据：

CREATE TABLE rowTocolumn( [id] [int] IDENTITY(1,1) NOT NULL, [sid] [int] NULL, [course] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, [grade] [float] NULL, CONSTRAINT [PK_rowTocolumn] PRIMARY KEY CLUSTERED ( [id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY])

insert into rowTocolumn(id, sid, course, grade)values (1, '20060101', '语文', 98.1);insert into rowTocolumn(id, sid, course, grade)values (2, '20060102', '语文', 65.5);insert into rowTocolumn(id, sid, course, grade)values (3, '20060101', '数学', 87.6);insert into rowTocolumn(id, sid, course, grade)values (4, '20060102', '数学', 88.6);insert into rowTocolumn(id, sid, course, grade)values (5, '20060101', '英语', 65.2);insert into rowTocolumn(id, sid, course, grade)values (6, '20060102', '英语', 79.9);insert into rowTocolumn(id, sid, course, grade)values (7, '20060103', '语文', 61.2);insert into rowTocolumn(id, sid, course, grade)values (8, '20060103', '数学', 66.5);insert into rowTocolumn(id, sid, course, grade)values (9, '20060103', '英语', 83.9);

sqlserver解决办法：

select sid, 语文 = isnull(sum(case course when '语文' then grade end),0) , 数学 = isnull(sum(case course when '数学' then grade end),0) , 英语 = isnull(sum(case course when '英语' then grade end),0) from rowtocolumngroup by sidorder by sid

oracle建表初始化：

create table SC( id NUMBER, sid VARCHAR2(10), cid VARCHAR2(10), grade NUMBER(5,1));

insert into SC (id, sid, cid, grade)values (1, '20060101', '语文', 98.1);insert into SC (id, sid, cid, grade)values (2, '20060102', '语文', 65.5);insert into SC (id, sid, cid, grade)values (3, '20060101', '数学', 87.6);insert into SC (id, sid, cid, grade)values (4, '20060102', '数学', 88.6);insert into SC (id, sid, cid, grade)values (5, '20060101', '英语', 65.2);insert into SC (id, sid, cid, grade)values (6, '20060102', '英语', 79.9);insert into SC (id, sid, cid, grade)values (7, '20060103', '语文', 61.2);insert into SC (id, sid, cid, grade)values (8, '20060103', '数学', 66.5);insert into SC (id, sid, cid, grade)values (9, '20060103', '英语', 83.9);

oracle解决方法1:

select sid, nvl(sum(case cid when '语文' then grade end),0) 语文, nvl(sum(case cid when '数学' then grade end),0) 数学, nvl(sum(case cid when '英语' then grade end),0) 英语from scgroup by sidorder by sid;

oracle解决方法2：

select sid, nvl(sum(decode(cid,'语文',grade)),0) 语文, nvl(sum(decode(cid,'数学',grade)),0) 数学, nvl(sum(decode(cid,'英语',grade)),0) 英语from scgroup by sidorder by sid;

原文章浏览量：4813

原文章分类：数据库

原文链接：https://www.iteye.com/blog/lizhiyu-752453

行转列 oracle sql decode