一列转多行与多行转一列

ifs_user查用户号，用户姓名，用户岗位，用户岗位需要关联岗位表ifs_station，但有的用户兼岗，怎样才能将商位ID转换为对应的中文名。此处涉及到列转多行与多行转列的知识。
两个表结构：
create table IFS_USER
(
  userid           VARCHAR2(23) not null,
  brid             VARCHAR2(23),
  subbankid        VARCHAR2(23),
  recycleflag      NUMBER(1) default 0,
  recycledt        DATE,
  recycleuserid    VARCHAR2(23),
  username         VARCHAR2(40),
  userpyname       VARCHAR2(100),
  usercode         VARCHAR2(23),
  userpswd         VARCHAR2(100),
  roleid           VARCHAR2(8),
  staid            VARCHAR2(250),
  department       VARCHAR2(60),
  position         VARCHAR2(60),
  isprocm          VARCHAR2(2),
  techtitle        VARCHAR2(60),
  sex              VARCHAR2(1),
  birthyear        VARCHAR2(4),
  birthmonday      VARCHAR2(6),
  marriage         VARCHAR2(2),
  education        VARCHAR2(2),
  officephone      VARCHAR2(20),
  homephone        VARCHAR2(20),
  mobilephone      VARCHAR2(20),
  fax              VARCHAR2(20),
  email            VARCHAR2(50),
  emailpswd        VARCHAR2(100),
  userstatus       VARCHAR2(1),
  uservalidstatus  VARCHAR2(2),
  userlockstatus   VARCHAR2(2),
  lockdt           DATE,
  userloginstatus  VARCHAR2(2),
  lastlogindt      DATE,
  invalidlogintime NUMBER(5),
  invalidlogindt   DATE,
  updatepswddt     DATE,
  enableflag       VARCHAR2(2),
  enabledt         DATE,
  ispresetup       VARCHAR2(2),
  userbid          VARCHAR2(23),
  truststatus      VARCHAR2(6),
  trustmode        VARCHAR2(6),
  truststartdt     DATE,
  trustclosedt     DATE,
  lastinvalidip    VARCHAR2(20),
  lastvalidip      VARCHAR2(20),
  skinname         VARCHAR2(20),
  pendids          VARCHAR2(1000),
  university       VARCHAR2(50),
  userlevel        VARCHAR2(10),
  specialty        VARCHAR2(50),
  lastcheck        VARCHAR2(2),
  cmbcdate         DATE,
  pactbeginym      VARCHAR2(6),
  pactendym        VARCHAR2(6),
  certflag         VARCHAR2(2),
  qualityflag      VARCHAR2(2),
  certtype         VARCHAR2(100),
  orgid            VARCHAR2(8),
  traindesc        VARCHAR2(1000),
  userdesc         VARCHAR2(1000),
  subbrid          VARCHAR2(23),
  laststaid        VARCHAR2(8),
  userno           VARCHAR2(23)
)
;
comment on table IFS_USER
  is '用户表';
comment on column IFS_USER.userid
  is '用户ID';
comment on column IFS_USER.brid
  is '所属机构';
comment on column IFS_USER.subbankid
  is '所属一级支行';
comment on column IFS_USER.recycleflag
  is '回收标志';
comment on column IFS_USER.recycledt
  is '回收日期';
comment on column IFS_USER.recycleuserid
  is '回收用户';
comment on column IFS_USER.username
  is '用户名称';
comment on column IFS_USER.userpyname
  is '用户英文名称';
comment on column IFS_USER.usercode
  is '用户代码';
comment on column IFS_USER.userpswd
  is '用户密码';
comment on column IFS_USER.roleid
  is '角色';
comment on column IFS_USER.staid
  is '岗位';
comment on column IFS_USER.department
  is '部门';
comment on column IFS_USER.position
  is '职位';
comment on column IFS_USER.isprocm
  is '是否专职客户经理';
comment on column IFS_USER.sex
  is '性别';
comment on column IFS_USER.birthyear
  is '出生年';
comment on column IFS_USER.birthmonday
  is '出生月日';
comment on column IFS_USER.marriage
  is '婚姻状况';
comment on column IFS_USER.education
  is '教育程度';
comment on column IFS_USER.officephone
  is '办公电话';
comment on column IFS_USER.homephone
  is '家庭电话';
comment on column IFS_USER.mobilephone
  is '手机号码';
comment on column IFS_USER.fax
  is '传真';
comment on column IFS_USER.email
  is '电子邮箱';
comment on column IFS_USER.emailpswd
  is '邮箱密码';
comment on column IFS_USER.userstatus
  is '用户状态';
comment on column IFS_USER.uservalidstatus
  is '有效状态';
comment on column IFS_USER.userlockstatus
  is '锁定状态';
comment on column IFS_USER.lockdt
  is '锁定日期';
comment on column IFS_USER.userloginstatus
  is '登录状态';
comment on column IFS_USER.lastlogindt
  is '上次登录日期';
comment on column IFS_USER.invalidlogintime
  is '无效登录次数';
comment on column IFS_USER.invalidlogindt
  is '无效登录日期';
comment on column IFS_USER.updatepswddt
  is '更新密码日期';
comment on column IFS_USER.enableflag
  is '是否可用';
comment on column IFS_USER.enabledt
  is '可用日期';
comment on column IFS_USER.ispresetup
  is '是否预设';
comment on column IFS_USER.userbid
  is 'B角用户';
comment on column IFS_USER.truststatus
  is '托管状态';
comment on column IFS_USER.trustmode
  is '托管模式';
comment on column IFS_USER.truststartdt
  is '托管开始日期';
comment on column IFS_USER.trustclosedt
  is '托管结束日期';
comment on column IFS_USER.lastinvalidip
  is '上次无效登陆IP';
comment on column IFS_USER.lastvalidip
  is '上次有效登陆IP';
comment on column IFS_USER.skinname
  is '界面方案名称';
comment on column IFS_USER.pendids
  is '备选ID';
comment on column IFS_USER.university
  is '毕业学校';
comment on column IFS_USER.userlevel
  is '用户等级';
comment on column IFS_USER.specialty
  is '专业';
comment on column IFS_USER.lastcheck
  is '上季度考核升降';
comment on column IFS_USER.cmbcdate
  is '进入银行日期';
comment on column IFS_USER.pactbeginym
  is '签订劳动合同日';
comment on column IFS_USER.pactendym
  is '结束劳动合同日';
comment on column IFS_USER.certflag
  is '是否持有证书';
comment on column IFS_USER.qualityflag
  is '是否获得上岗证';
comment on column IFS_USER.certtype
  is '证书类型';
comment on column IFS_USER.orgid
  is '组织编号';
comment on column IFS_USER.traindesc
  is '培训描述';
comment on column IFS_USER.userdesc
  is '用户描述';
comment on column IFS_USER.subbrid
  is '所属支行';
comment on column IFS_USER.laststaid
  is '最后登录岗位';
comment on column IFS_USER.userno
  is '行员编号';
create unique index PK_IFS_USER on IFS_USER (USERID);


create table IFS_STATION
(
  staid         VARCHAR2(8) not null,
  recycleflag   NUMBER(1) default 0,
  recycledt     DATE,
  recycleuserid VARCHAR2(8),
  staname       VARCHAR2(60),
  stacode       VARCHAR2(40),
  stadesc       VARCHAR2(1000),
  ispresetup    VARCHAR2(6),
  brlevels      VARCHAR2(20),
  orgid         VARCHAR2(8),
  staidtype     CHAR(1)
)
;
comment on table IFS_STATION
  is '岗位信息表  ';
comment on column IFS_STATION.staid
  is '岗位ID      ';
comment on column IFS_STATION.recycleflag
  is '删除标志    ';
comment on column IFS_STATION.recycledt
  is '删除时间    ';
comment on column IFS_STATION.recycleuserid
  is '删除用户    ';
comment on column IFS_STATION.staname
  is '岗位名称    ';
comment on column IFS_STATION.stacode
  is '岗位代码    ';
comment on column IFS_STATION.stadesc
  is '岗位说明    ';
comment on column IFS_STATION.ispresetup
  is '是否系统预设';
comment on column IFS_STATION.brlevels
  is '可用机构级别';
comment on column IFS_STATION.orgid
  is '组织ID      ';
comment on column IFS_STATION.staidtype
  is '岗位类型0-共用 1-对私 2-对公';
alter table IFS_STATION
  add constraint PK_IFS_STATION primary key (STAID);


---------------------------------------------------------------------------------------------------------------
实现SQL如下：
--行转列
SELECT t4.userid,t4.username,to_char(wmsys.wm_concat(t4.statid)),to_char(wmsys.wm_concat(t4.staname))
FROM(
SELECT t2.*,t3.staname FROM (
SELECT * FROM (
--按"."分隔权限，列内字符串转多行
SELECT u.userid,u.username,regexp_substr(u.staid,'[^.]+',1,LEVEL) AS statid FROM ifs_user u
CONNECT BY LEVEL <= regexp_count(u.staid,'.')+1
and userid = prior userid
and prior dbms_random.value is not null
) t WHERE t.statid IS NOT NULL) t2 
--与权限表关联
LEFT JOIN 
ifs_station t3
ON t2.statid=t3.staid) t4 GROUP BY t4.userid,t4.username;
--行转列
SELECT t4.userid,t4.username,to_char(wmsys.wm_concat(t4.statid)),to_char(wmsys.wm_concat(t4.staname))
FROM(
SELECT t2.*,t3.staname FROM (
SELECT * FROM (
--按"."分隔权限，列内字符串转多行
SELECT u.userid,u.username,regexp_substr(u.staid,'[^.]+',1,LEVEL) AS statid FROM ifs_user u
CONNECT BY LEVEL <= regexp_count(u.staid,'.')+1
and userid = prior userid
and prior dbms_random.value is not null
) t WHERE t.statid IS NOT NULL) t2 
--与权限表关联
LEFT JOIN 
ifs_station t3
ON t2.statid=t3.staid) t4 GROUP BY t4.userid,t4.username;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29668973/viewspace-1212126/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29668973/viewspace-1212126/