Oracle 函数 wm_concat 将列转行 用法 实例

        这篇文章主要记录下我工作中遇到sql语句查询的问题，工作中有个需求是做问卷调查，然后统计导出数据，其中有个问题是多选题，存的是答案表对应的答案的id（以逗号分隔），然后查询的时候怎么样才能把这个问题的答案也查出来拼接成逗号分隔，就用到了 WM_CONCAT 函数，下面就说一下具体用法。

      1. 首先看一下我的表结构，总共 有三张表 ，qv_question 记录了问题，qv_answer 记录了 问题对应的答案（单选，多选的选项，而不是调查回答的答案），qv_investreplay 这个记录了每个人 问卷调查 回答的 问题对应的答案，因此 一个人回答了多个问题，一个问题有多个答案，qv_investreplay 对qv_question 是一对多，同样 qv_question 对 qv_answer 也是一对多

      

— Create table
create table QV_QUESTION
(
  id            VARCHAR2(32) not null,
  questionorder NUMBER,
  questionname  VARCHAR2(300),
  questiontype  VARCHAR2(1),
  investid      VARCHAR2(32),
  addusername   VARCHAR2(50),
  addtime       DATE,
  adddept       VARCHAR2(200),
  addname       VARCHAR2(50),
  isdeleted     VARCHAR2(50),
  investname    VARCHAR2(200),
  allowwrite    VARCHAR2(50)
)
tablespace RMSFUSION
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );
— Add comments to the table
comment on table QV_QUESTION
  is ‘题目表’;
— Add comments to the columns
comment on column QV_QUESTION.questionorder
  is ‘题目排序’;
comment on column QV_QUESTION.questionname
  is ‘题目名称’;
comment on column QV_QUESTION.questiontype
  is ‘类型 1 :单选2：复选  3：文本框’;
comment on column QV_QUESTION.investid
  is ‘主题id’;
comment on column QV_QUESTION.isdeleted
  is ‘0:未删除 1：删除’;
comment on column QV_QUESTION.investname
  is ‘主题名称’;
comment on column QV_QUESTION.allowwrite
  is ‘如果是单选复选的话是否允许客户手动输入 0：不允许1允许’;
— Create/Recreate primary, unique and foreign key constraints
alter table QV_QUESTION
  add constraint PK_QV_QUESTION primary key (ID)
  using index
  tablespace RMSFUSION
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

— Create table
create table QV_ANSWER
(
  id           VARCHAR2(32) not null,
  answername   VARCHAR2(200),
  questionid   VARCHAR2(3200),
  addusername  VARCHAR2(50),
  addtime      DATE,
  adddept      VARCHAR2(200),
  addname      VARCHAR2(50),
  isdeleted    VARCHAR2(50),
  questionname VARCHAR2(200),
  ordernumber  NUMBER
)
tablespace RMSFUSION
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );
— Add comments to the table
comment on table QV_ANSWER
  is ‘问题答案表’;
— Add comments to the columns
comment on column QV_ANSWER.answername
  is ‘答案名称’;
comment on column QV_ANSWER.questionid
  is ‘题目id’;
comment on column QV_ANSWER.isdeleted
  is ‘0:未删除  1：已删除’;
comment on column QV_ANSWER.questionname
  is ‘题目名称’;
comment on column QV_ANSWER.ordernumber
  is ‘答案排序’;
— Create/Recreate primary, unique and foreign key constraints
alter table QV_ANSWER
  add constraint PK_QV_ANSWER primary key (ID)
  using index
  tablespace RMSFUSION
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

— Create table

create table QV_INVESTREPLAY

(

  id          VARCHAR2(32) not null,

  investid    VARCHAR2(32),

  questionid  VARCHAR2(32),

  answerid    VARCHAR2(255),

  useranswer  VARCHAR2(500),

  addusername VARCHAR2(50),

  addtime     DATE,

  adddept     VARCHAR2(200),

  addname     VARCHAR2(50),

  isreplay    VARCHAR2(30),

  allowanswer VARCHAR2(255)

)

tablespace RMSFUSION

  pctfree 10

  initrans 1

  maxtrans 255

  storage

  (

    initial 64

    minextents 1

    maxextents unlimited

  );

— Add comments to the table

comment on table QV_INVESTREPLAY

  is ‘问卷反馈表’;

— Add comments to the columns

comment on column QV_INVESTREPLAY.investid

  is ‘问卷id’;

comment on column QV_INVESTREPLAY.questionid

  is ‘题目id’;

comment on column QV_INVESTREPLAY.answerid

  is ‘答案id’;

comment on column QV_INVESTREPLAY.useranswer

  is ‘用户答案（文本框）’;

comment on column QV_INVESTREPLAY.addusername

  is ‘参与者工号’;

comment on column QV_INVESTREPLAY.addtime

  is ‘参与时间’;

comment on column QV_INVESTREPLAY.adddept

  is ‘参与者部门’;

comment on column QV_INVESTREPLAY.addname

  is ‘参与者姓名’;

comment on column QV_INVESTREPLAY.isreplay

  is ‘是否已经回复 0：未回复 1：已回复’;

comment on column QV_INVESTREPLAY.allowanswer

  is ‘单选多选题客户补充答案’;

— Create/Recreate primary, unique and foreign key constraints

alter table QV_INVESTREPLAY

  add constraint PK_QV_INVESTREPLAY primary key (ID)

  using index

  tablespace RMSFUSION

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 64K

    minextents 1

    maxextents unlimited

  );

      2. 这里只看 有个多选题， “你希望影吧播放的电影类型是？”，以及这个问题的答案。

         这是问卷调查 回答的答案，可以看到，存的 都是 qv_answer 表对应的 id，并且以 逗号分隔

     3. 如何用 wm_concat 函数 查出下列结果，sql 语句 如下：

        select a.id,a.addusername,a.addname,a.adddept,a.addtime,to_char(WM_CONCAT(b.answername)) 类型
                      from qv_investreplay a,qv_answer b
                              where  b.questionid = ‘422517’ and  instr(‘,’||a.answerid||’,’,’,’||b.id||’,’)>0
                                     group by a.id,a.addusername,a.addname,a.adddept,a.addtime

        关联条件 使用 instr(‘,’||a.answerid||’,’,’,’||b.id||’,’)>0，这样就可以关联出所有 id 对应的答案，这时候 使用 to_char(WM_CONCAT(b.answername)) 类型 将答案以逗号拼接，其他查询字段，都要在 group by 里。

        也可以用with as 做临时表 an （qv_investreplay 也可以写一个with as，只把相关字段写出来，便于测试），简单写下 如下：

              with an
                   as (
                          select id,answername from qv_answer where questionid = ‘422517’
                    )
             select a.id,a.addusername,a.addname,a.adddept,a.addtime,to_char(WM_CONCAT(b.answername)) 类型
                      from qv_investreplay a,an b
                                where instr(‘,’||a.answerid||’,’,’,’||b.id||’,’)>0
                                      group by a.id,a.addusername,a.addname,a.adddept,a.addtime

      3. 有时安装的 oracle 可能版本 并没有 wm_concat 函数，这时可以自定义，如下sql语句：

          Oracle 11gR2和12C上已经摒弃了wm_concat函数，当时我们很多程序员在程序中确使用了该函数，导致程序出现错误，为了减轻程序员修改程序的工作量，只有通过手工创建个wm_concat函数，来临时解决该问题，但是注意，及时创建了该函数，在使用的过程中，也需要用to_char(wm_concat())方式，才能完全替代之前的应用。

一.解锁wmsys用户
alter user wmsys account unlock;

二.创建包、包体和函数
以wmsys用户登录数据库，执行下面的命令

CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT
— AUTHID CURRENT_USER AS OBJECT
(
CURR_STR VARCHAR2(32767),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER
);
/

–定义类型body:
CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL
IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
SCTX := WM_CONCAT_IMPL(NULL) ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,
P1 IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
IF(CURR_STR IS NOT NULL) THEN
CURR_STR := CURR_STR || ‘,’ || P1;
ELSE
CURR_STR := P1;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURNVALUE := CURR_STR ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,
SCTX2 IN WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
IF(SCTX2.CURR_STR IS NOT NULL) THEN
SELF.CURR_STR := SELF.CURR_STR || ‘,’ || SCTX2.CURR_STR ;
END IF;
RETURN ODCICONST.SUCCESS;
END;
END;
/
–自定义行变列函数:
CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2)
RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL ;
/

三.创建同义词并授权

create public synonym WM_CONCAT_IMPL for wmsys.WM_CONCAT_IMPL
/
create public synonym wm_concat for wmsys.wm_concat
/

grant execute on WM_CONCAT_IMPL to public
/
grant execute on wm_concat to public
/