行转列 oracle sql decode

                                          数据库行转列

困惑已久的行转列的问题 恍然大悟，主要问题出现在对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 rowtocolumn
group by sid
order  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 sc
group by sid
order 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 sc
group by sid
order by sid;