LISTAGG 行转列

创建tablespace

create tablespace mine_data datafile 'D:\app\ZHANGLU6\oradata\orcl\mine.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;

创建表空间用户

create user kaka identified by Password1 default tablespace mine_data;

给用户赋权限

grant create session to kaka;

grant resource,connect to kaka;

grant unlimited tablespace to kaka;

现在就可以使用这个用户了。

创建两个表：

CREATE TABLE KAKA_ORDER
(
  ORDER_ID NUMBER(10) PRIMARY KEY,
    BK_NO VARCHAR2(10),
  CREATE_BY VARCHAR2(10),
  OFFICE_CODE VARCHAR2(10)
);

CREATE TABLE KAKA_PARTY
(
  PT_ID NUMBER(10) PRIMARY KEY,
  ORDER_ID NUMBER(10),
    P_TYPE VARCHAR2(10),
  DISPLAY_NAME VARCHAR2(50),
  ADDRESS VARCHAR2(100)
);

为两个表创建sequence：

CREATE SEQUENCE KAKA_ORDER_SEQ
MINVALUE 1
MAXVALUE 99999999999999
START WITH 1
INCREMENT BY 1
NOCACHE
ORDER;

CREATE SEQUENCE KAKA_PARTY_SEQ
MINVALUE 1
MAXVALUE 99999999999999
START WITH 1
INCREMENT BY 1
NOCACHE
ORDER;

向两个表中插入测试数据：

INSERT INTO KAKA_PARTY VALUES(KAKA_PARTY_SEQ.nextval, 5, 'CNG', 'receiver', 'Tang jia');
INSERT INTO KAKA_PARTY VALUES(KAKA_PARTY_SEQ.nextval, 6, 'CNG', 'fa person', 'Nan ruan');
INSERT INTO KAKA_PARTY VALUES(KAKA_PARTY_SEQ.nextval, 5, 'SHP', 'Shipper', 'Guang zhou');
INSERT INTO KAKA_PARTY VALUES(KAKA_PARTY_SEQ.nextval, 6, 'SHP', 'fa huo ren', 'Tang jia');

INSERT INTO KAKA_ORDER VALUES(KAKA_ORDER_SEQ.nextval, '20160327', 'KAKA', 'ZHA');
INSERT INTO KAKA_ORDER VALUES(KAKA_ORDER_SEQ.nextval, '20160328', 'KAKA', 'SHA');

行转列查询：

SELECT * FROM KAKA_ORDER m left join
(SELECT p.ORDER_ID,
      MAX(DECODE(P_TYPE, 'SHP', DISPLAY_NAME, NULL)) shipper_name,
      MAX(DECODE(P_TYPE, 'CNG', DISPLAY_NAME, NULL)) consignee_name
    FROM KAKA_PARTY p
    WHERE p.P_TYPE IN ('SHP', 'CNG')
    GROUP BY p.ORDER_ID
    ) pt
    on m.ORDER_ID = pt.ORDER_ID;

SELECT m.*,
    (SELECT LISTAGG(pt.DISPLAY_NAME, ';') WITHIN GROUP(
    ORDER BY pt.ORDER_ID)
    FROM KAKA_PARTY pt
    WHERE m.ORDER_ID = pt.ORDER_ID
    GROUP BY pt.ORDER_ID
    ) AS Party_INFO
  FROM KAKA_ORDER m;