1. select concat(id,username) str from app_user
2. select id||username str from app_user

1. select id,wm_concat(name) name from test group by id;

1. -------- 适用范围：8i,9i,10g及以后版本  （ MAX + DECODE ）
2. select id, max(decode(rn, 1, name, null)) || max(decode(rn, 2, ','||name, null)) || max(decode(rn, 3, ','||name, null)) str
3. from (select id, name ,row_number() over(partition by id order by name) as rn from test) t group by id order by 1;
4. -------- 适用范围：8i,9i,10g及以后版本 （ ROW_NUMBER + LEAD ）
5. select id, str from (select id,row_number() over(partition by id order by name) as rn,name || lead(',' || name, 1)
6. over(partition by id order by name) ||  lead(',' || name, 2) over(partition by id order by name) || lead(',' || name, 3)
7. over(partition by id order by name) as str from test) where rn = 1 order by 1;
8. -------- 适用范围：10g及以后版本 （ MODEL ）
9. select id, substr(str, 2) str from test model return updated rows partition by(id) dimension by(row_number()
10. over(partition by id order by name) as rn) measures (cast(name as varchar2(20)) as str) rules upsert iterate(3)
11. until(presentv(str[iteration_number + 2], 1, 0)=0) (str[0] = str[0] || ',' || str[iteration_number + 1]) order by 1;
12. -------- 适用范围：8i,9i,10g及以后版本 （ MAX + DECODE ）
13. select t.id id, max(substr(sys_connect_by_path(t.name, ','), 2)) str from (select id, name, row_number()
14. over(partition by id order by name) rn from test) t start with rn = 1 connect by rn = prior rn + 1 and id = prior id
15. group by t.id;

/*物料 需要数量 需要仓库 现存量仓库 现存量仓库数量 批次
A1
2 C1 C1         20         123
A1
2 C1 C2         30         111
A1
2 C1 C2         20         222
A1
2 C1 C3         10         211
A2
3 C4 C1         40         321
A2
3 C4 C4         50         222
A2
3 C4 C4         60         333
A2
3 C4 C5         70         223
我需要把上面的查询结果转换为下面的。
物料 需要数量 需要仓库 C1 C2 C3 C4 C5
A1
2 C1 20 50 10 0 0
A2 3 C4 40 0 0 110
70
*/
---------------------------------------------------------------建表
----------------判断表是否存在
declare
num number;
begin
    select count(1) into num from user_tables where table_name='TEST';
    if num>0 then
      execute
immediate 'drop table TEST';
    end if;
end;
----------------建表
CREATE
TABLE TEST(
    WL
VARCHAR2(10),
    XYSL
INTEGER,
    XYCK
VARCHAR2(10),
    XCLCK
VARCHAR2(10),
    XCLCKSL
INTEGER,
    PC INTEGER
);
----------------第一部分测试数据
INSERT
INTO TEST VALUES('A1', 2, 'C1', 'C1' ,        20,         123);
INSERT
INTO TEST VALUES('A1', 2, 'C1', 'C2' ,        30,         111);
INSERT
INTO TEST VALUES('A1', 2, 'C1', 'C2' ,        20,         222);
INSERT
INTO TEST VALUES('A1', 2, 'C1', 'C3' ,        10,         211);
INSERT
INTO TEST VALUES('A2', 3, 'C4', 'C1' ,        40,         321);
INSERT
INTO TEST VALUES('A2', 3, 'C4', 'C4' ,        50,         222);
INSERT
INTO TEST VALUES('A2', 3, 'C4', 'C4' ,        60,         333);
INSERT
INTO TEST VALUES('A2', 3, 'C4', 'C5' ,        70,         223);
COMMIT;
--select * from test;
---------------------------------------------------------------行转列的存储过程
CREATE OR REPLACE PROCEDURE P_TEST IS
  V_SQL VARCHAR2(2000);
  CURSOR CURSOR_1 IS SELECT DISTINCT T.XCLCK FROM TEST T ORDER BY XCLCK;
   
    BEGIN
      V_SQL
:= 'SELECT WL,XYSL,XYCK';

FOR V_XCLCK IN
CURSOR_1
      LOOP
        V_SQL := V_SQL || ',' || 'SUM(DECODE(XCLCK,''' || V_XCLCK.XCLCK ||
                 ''',XCLCKSL,0)) AS ' || V_XCLCK.XCLCK;
      END LOOP;
     
      V_SQL := V_SQL
|| ' FROM TEST GROUP BY WL,XYSL,XYCK ORDER BY WL,XYSL,XYCK';
      --DBMS_OUTPUT.PUT_LINE(V_SQL);
      V_SQL :=
'CREATE OR REPLACE VIEW RESULT  AS '||  V_SQL;
      --DBMS_OUTPUT.PUT_LINE(V_SQL);
      EXECUTE
IMMEDIATE V_SQL;
    END;
----------------------------------------------------------------结果
----------------执行存储过程，生成视图
BEGIN
  P_TEST;              
END;
----------------结果
SELECT
* FROM RESULT T;
WL                                            XYSL
XYCK               C1         C2         C3         C4         C5
----------
--------------------------------------- ---------- ---------- ----------
---------- ---------- ----------
A1                                               2
C1                 20         50         10          0          0
A2                                               3
C4                 40          0          0        110         70

----------------第二部分测试数据
INSERT
INTO TEST VALUES('A1', 2, 'C1', 'C6' ,        20,         124);
INSERT
INTO TEST VALUES('A2', 2, 'C1', 'C7' ,        30,         121);
INSERT
INTO TEST VALUES('A3', 2, 'C1', 'C8' ,        20,         322);
COMMIT;
----------------报告存储过程，生成视图
BEGIN
  P_TEST;              
END;
----------------结果
SELECT
* FROM RESULT T;
WL     XYSL
XYCK          C1       C2         C3         C4         C5         C6         C7         C8
-----
----- -------- ------- -------- ---------- ---------- ---------- ----------
---------- ----------
A1        2 C1            20       50         10          0          0         20          0          0
A2        2 C1             0        0          0          0          0          0         30          0
A2        3 C4            40        0          0        110         70          0          0          0
A3        2 C1             0        0          0          0          0          0          0         20
---------------
删除实体
DROP VIEW RESULT;
DROP
PROCEDURE P_TEST;
DROP TABLE TEST;