用Oracle的decode函数进行列转行
----------------------------------------------------------------
测试用：
CREATE TABLE TB_PAIBAN(
STIME CHAR(8),
DUTY VARCHAR(10),
SHIFT VARCHAR(10),
UNAME VARCHAR(20),
PRIMARY KEY (STIME,DUTY,SHIFT,UNAME)
);
INSERT INTO TB_PAIBAN(STIME,DUTY,SHIFT,UNAME) VALUES('20100101','岗位1','白班','其');
INSERT INTO TB_PAIBAN(STIME,DUTY,SHIFT,UNAME) VALUES('20100102','岗位1','白班','雄');
INSERT INTO TB_PAIBAN(STIME,DUTY,SHIFT,UNAME) VALUES('20100103','岗位1','白班','文');
INSERT INTO TB_PAIBAN(STIME,DUTY,SHIFT,UNAME) VALUES('20100104','岗位1','白班','其');
INSERT INTO TB_PAIBAN(STIME,DUTY,SHIFT,UNAME) VALUES('20100105','岗位1','白班','其');
INSERT INTO TB_PAIBAN(STIME,DUTY,SHIFT,UNAME) VALUES('20100106','岗位1','白班','其');
INSERT INTO TB_PAIBAN(STIME,DUTY,SHIFT,UNAME) VALUES('20100107','岗位1','白班','其');
INSERT INTO TB_PAIBAN(STIME,DUTY,SHIFT,UNAME) VALUES('20100101','岗位1','夜班','其');
INSERT INTO TB_PAIBAN(STIME,DUTY,SHIFT,UNAME) VALUES('20100102','岗位1','夜班','治');
INSERT INTO TB_PAIBAN(STIME,DUTY,SHIFT,UNAME) VALUES('20100103','岗位1','夜班','文');
INSERT INTO TB_PAIBAN(STIME,DUTY,SHIFT,UNAME) VALUES('20100104','岗位1','夜班','其');
INSERT INTO TB_PAIBAN(STIME,DUTY,SHIFT,UNAME) VALUES('20100105','岗位1','夜班','其');
INSERT INTO TB_PAIBAN(STIME,DUTY,SHIFT,UNAME) VALUES('20100106','岗位1','夜班','其');
INSERT INTO TB_PAIBAN(STIME,DUTY,SHIFT,UNAME) VALUES('20100107','岗位1','夜班','其');
create or replace view V_PAIBAN(岗位,班次,星期一,星期二,星期三,星期四,星期五,星期六,星期日) as
select
DUTY,
SHIFT,
MAX( DECODE( SUBSTRB(STIME,7,2),'01', UNAME ,NULL)),
MAX( DECODE( SUBSTRB(STIME,7,2),'02', UNAME ,NULL)),
MAX( DECODE( SUBSTRB(STIME,7,2),'03', UNAME ,NULL)),
MAX( DECODE( SUBSTRB(STIME,7,2),'04', UNAME ,NULL)),
MAX( DECODE( SUBSTRB(STIME,7,2),'05', UNAME ,NULL)),
MAX( DECODE( SUBSTRB(STIME,7,2),'06', UNAME ,NULL)),
MAX( DECODE( SUBSTRB(STIME,7,2),'07', UNAME ,NULL))
from
TB_PAIBAN
GROUP BY DUTY,SHIFT;
SELECT * FROM V_PAIBAN;
