oracle 多行,一行转换(10g以上)

--1. 多行转换成一行

-- 1.1 table 语句
CREATE TABLE "LOCAL_WPC04_01"."A_ROW_STR"
   ( "ID" NUMBER(*,0),
 "COL" VARCHAR2(10 BYTE)
   );
Insert into LOCAL_WPC04_01.A_ROW_STR (ID,COL) values (1,'a');
Insert into LOCAL_WPC04_01.A_ROW_STR (ID,COL) values (1,'b');
Insert into LOCAL_WPC04_01.A_ROW_STR (ID,COL) values (1,'c');
Insert into LOCAL_WPC04_01.A_ROW_STR (ID,COL) values (2,'a');
Insert into LOCAL_WPC04_01.A_ROW_STR (ID,COL) values (2,'d');
Insert into LOCAL_WPC04_01.A_ROW_STR (ID,COL) values (2,'e');
Insert into LOCAL_WPC04_01.A_ROW_STR (ID,COL) values (3,'c');
-- 1.2 转换语句 by Hierarchical
SELECT t.id id,
  SUBSTR(sys_connect_by_path(t.col, ','), 2) str
FROM
  (SELECT id,
    col,
    row_number() over(PARTITION BY id ORDER BY col) rn
  FROM a_row_str
  ) t
WHERE connect_by_isleaf = 1
  START WITH rn         = 1
  CONNECT BY rn         = PRIOR rn + 1
AND id                  = PRIOR id;

--2.一行转换成多行

--2.1 table 语句
CREATE TABLE A_STR_ROW
   ( "ID" NUMBER(*,0),
 "STR" VARCHAR2(32 BYTE)
   )；
Insert into LOCAL_WPC04_01.A_STR_ROW (ID,STR) values (1,'a,b,c');
Insert into LOCAL_WPC04_01.A_STR_ROW (ID,STR) values (2,'a,d,e');
Insert into LOCAL_WPC04_01.A_STR_ROW (ID,STR) values (3,'c');

--2.2 转换语句
SELECT id,
LEVEL AS p,
rtrim(regexp_substr(str || ',', '.*?' || ',', 1, LEVEL), ',') AS cv
FROM a_str_row
CONNECT BY id = PRIOR id
AND PRIOR dbms_random.VALUE IS NOT NULL
AND LEVEL <=
length(regexp_replace(str || ',', '[^' || ',' || ']', NULL))
ORDER BY 1, 2;

--3. 多行转换成一行 for user_tab_columns

SELECT DISTINCT t.table_name tabname,
    LISTAGG(t.column_name,',') within GROUP(
  ORDER BY t.column_id ASC) OVER (PARTITION BY table_name) AS colname
  FROM user_tab_columns t