oracle 行列轉換 列名數字

標籤：str   sele   ref   constrain   杭州   統計   alt   圖片   nvarchar   

oracle 行列轉換列名如果是數字，用雙引號包住  如下：

 

--  建表

create table workinfo
(
wid integer primary key,
sid integer ,
CONSTRAINT sid foreign key(sid) references student(sid),
city nvarchar2(100),-- 就業城市
wage number(10,2),-- 月薪
workdate date,-- 就業時間
eid integer ,
CONSTRAINT eid foreign key(eid) references Emp(eid)-- 介紹人
);
--  假資料

insert into workinfo values(1001,8001,‘上海‘,5000,to_date(‘2009-3-5‘,‘yyyy-MM-dd‘),6005);
insert into workinfo values(1002,8002,‘上海‘,4500,to_date(‘2010-3-5‘,‘yyyy-MM-dd‘),6005);
insert into workinfo values(1003,8005,‘上海‘,5500,to_date(‘2009-3-5‘,‘yyyy-MM-dd‘),6005);
insert into workinfo values(1004,8003,‘杭州‘,4000,to_date(‘2009-12-5‘,‘yyyy-MM-dd‘),6006);
insert into workinfo values(1005,8004,‘杭州‘,4500,to_date(‘2007-12-5‘,‘yyyy-MM-dd‘),6006);
insert into workinfo values(1006,8006,‘南京‘,4000,to_date(‘2009-12-5‘,‘yyyy-MM-dd‘),6006);
insert into workinfo values(1007,8008,‘杭州‘,4000,to_date(‘2009-12-5‘,‘yyyy-MM-dd‘),6006);
insert into workinfo values(1008,8010,‘上海‘,4000,to_date(‘2007-12-5‘,‘yyyy-MM-dd‘),6007);
insert into workinfo values(1009,8007,‘杭州‘,4400,to_date(‘2008-12-5‘,‘yyyy-MM-dd‘),6007);
insert into workinfo values(1010,8009,‘杭州‘,4000,to_date(‘2009-12-5‘,‘yyyy-MM-dd‘),6007);

-- 統計各地區每年就業的人數，顯示如下

select a.city,
              sum(case when a.years=‘2007‘ then a.rs else 0 end) "2007",
              sum(case when a.years=‘2008‘ then a.rs else 0 end) "2008",
              sum(case when a.years=‘2009‘ then a.rs else 0 end) "2008",
              sum(case when a.years=‘2010‘ then a.rs else 0 end) "2009"
from
(
    select city,to_char(workdate,‘yyyy‘) years,count(sid)rs
    from workinfo
    group by city,to_char(workdate,‘yyyy‘)
)a
group by a.city

 

結果

oracle 行列轉換 列名數字