行转列逗号隔开&逗号隔开列转行

ORACLE：

 

SQL> create table t(id int,name varchar2(30));

表已创建。

SQL> insert into t values(1,'a');

已创建 1 行。

SQL> insert into t values(1,'b');

已创建 1 行。

SQL> insert into t values(1,'c');

已创建 1 行。

SQL> insert into t values(2,'aa');

已创建 1 行。

SQL> insert into t values(2,'bb');

已创建 1 行。

SQL> commit;

提交完成。

--第一种
SQL> select wmsys.wm_concat(distinct name) from t;

WMSYS.WM_CONCAT(DISTINCTNAME)
---------------------------------------------------------------------

a,aa,b,bb,c

SQL> col con_name format a10
SQL> select id,wmsys.wm_concat(distinct name) con_name from t group by id;

        ID CON_NAME
---------- ----------
         1 a,b,c
         2 aa,bb

--第二种
SQL> SELECT TRIM(',' FROM SYS.STRAGG(NAME||NVL2(NAME,',',''))) CON_NAME FROM T;

CON_NAME
----------
a,b,c,aa,bb


--第三种
select id,sys_connect_by_path(name,'>')  con_name
from (select id,name,row_number() over(partition by id order by name) rn
     from (select id,name from t ))t
start with t.rn=1
connect by t.id=prior t.id and t.rn-1=prior t.rn
/
        ID CON_NAME
---------- --------------------
         1 >a
         1 >a>b
         1 >a>b>c
         2 >aa
         2 >aa>bb
         2 >aa>bb>china
         2 >aa>bb>china>china
SQL> edit
已写入 file afiedt.buf

  1  select id,substr(max(sys_connect_by_path(name,'>')),2)  con_name
  2  from (select id,name,row_number() over(partition by id order by name) rn
  3       from (select id,name from t ))t
  4  start with t.rn=1
  5  connect by t.id=prior t.id and t.rn-1=prior t.rn
  6* group by id
SQL> /

        ID CON_NAME
---------- --------------------
         1 a>b>c
         2 aa>bb>china>china
-- 当然也可以使用wm_concat，这个函数比较旧了 
第四种
with temp as(  
  select 'China' nation ,'Guangzhou' city from dual union all  
  select 'China' nation ,'Shanghai' city from dual union all  
  select 'China' nation ,'Beijing' city from dual union all  
  select 'USA' nation ,'New York' city from dual union all  
  select 'USA' nation ,'Bostom' city from dual union all  
  select 'Japan' nation ,'Tokyo' city from dual   
)  
select nation,listagg(city,',') within GROUP (order by city)  
from temp  
group by nation 

另外如果是要用引号的话使用：

select '''' || listagg(substr(name, 1, 30), q'{','}') within group(order by name) || ''''
from (select distinct name from demo); 

逗号隔开转多行：

SELECT REGEXP_SUBSTR ('1,2,3', '[^,]+', 1,rownum)
from dual connect by rownum<=LENGTH ('1,2,3') - LENGTH (regexp_replace('1,2,3', ',', ''))+1;

说明：正则函数regexp_substr跟substr类似，这里匹配模式意思是以逗号隔开的字符串，1代表位置，rownum代表第几次匹配

另外rownum也可以 替换为level

如果是老版本升级，没有wm_concat的话可以自己创建一个：

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 sys.WM_CONCAT_IMPL
/
create public synonym wm_concat for sys.wm_concat
/
 
grant execute on WM_CONCAT_IMPL to public
/
grant execute on wm_concat to public
/

mysql：

mysql> create table t(id int,name varchar(10));
Query OK, 0 rows affected (2.19 sec)

mysql> insert into t values(1,'a'),(1,'b'),(2,'c');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select concat(1,'a');
+---------------+
| concat(1,'a') |
+---------------+
| 1a            |
+---------------+
1 row in set (0.00 sec)

mysql> select concat(1,'a',null);  
+--------------------+
| concat(1,'a',null) |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.00 sec)

mysql> select concat_ws(1,'a',null);
+-----------------------+
| concat_ws(1,'a',null) |
+-----------------------+
| a                     |
+-----------------------+
1 row in set (0.00 sec)

mysql> select concat_ws('A','a',null);
+-------------------------+
| concat_ws('A','a',null) |
+-------------------------+
| a                       |
+-------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws(',','A','a',null);
+-----------------------------+
| concat_ws(',','A','a',null) |
+-----------------------------+
| A,a                         |
+-----------------------------+
1 row in set (0.00 sec)

mysql> -- 第一个参数是分隔符，并且会摒弃null
mysql> select group_concat(name) from t;
+--------------------+
| group_concat(name) |
+--------------------+
| a,b,c              |
+--------------------+
1 row in set (0.00 sec)

mysql> select group_concat(name) from t group by id;
+--------------------+
| group_concat(name) |
+--------------------+
| a,b                |
| c                  |
+--------------------+
2 rows in set (0.00 sec)

MSSQL：

create table ta (name varchar(10),value varchar(20));  
insert into ta values('a','beij');  
insert into ta values('a','shangh');  
insert into ta values('b','shenz');  
insert into ta values('b','chongq'); 
  
select (','+ value) 
from ta for xml path('');  

XML_F52E2B61-18A1-11d1-B105-00805F49916B
,beij,shangh,shenz,chongq

select  name,
       stuff((select ',' +  value
               from ta as t2
              where t2. name = t1.name
                FOR XML PATH('')),
             1,
             1,
             ''
             ) as str1
  from ta as t1
 --where t1.name = 'a'
 group by name;

name	str1
a	beij,shangh
b	shenz,chongq

逗号隔开转多行：

create table t1 (id int,value varchar(20));
insert into t1 (id,value) values(1,'China,usa');
insert into t1 (id,value) values(2,'Japan,Russia');

select a.id , value = substring(a.value , b.number , charindex(',' , a.value + ',' , b.number) - b.number) 
from t1 a join master..spt_values  b 
on b.type='p' and b.number between 1 and len(a.value)
where substring(',' + a.value , b.number , 1) = ','

统计逗号隔开的计数：

NOSQLDBA@prod> drop table t;

Table dropped.

NOSQLDBA@prod> create table t (text varchar(20));

Table created.

NOSQLDBA@prod> insert into t values('a,b,c');

1 row created.

NOSQLDBA@prod> insert into t values('b');

1 row created.

NOSQLDBA@prod> insert into t values('a');

1 row created.
NOSQLDBA@prod> col type1 for a20
NOSQLDBA@prod> with temp as
  2   (select text  from  t)
  3  select temp1.text type1, count(1) cnt
  4    from (select substr(text,
  5                        instr(text, ',', 1, rn) + 1,
  6                        instr(text, ',', 1, rn + 1) - instr(text, ',', 1, rn) - 1) text
  7            from (select ',' || t1.text || ',' text, t2.rn
  8                    from (select text,
  9                                 length(text) - length(replace(text, ',', '')) + 1 rn
 10                            from temp) t1,
 11                         (select rownum rn
 12                            from all_objects
 13                           where rownum <=
 14                                 (select max(length(text) -
 15                                             length(replace(text, ',', '')) + 1) rn
 16                                    from temp)) t2
 17                   where t1.rn >= t2.rn
 18                   order by text, rn)) temp1
 19   group by temp1.text
 20  union all
 21  select 'empty' as type1, count(*) as cnt
 22    from t
 23   where TEXT is null;

TYPE1                       CNT
-------------------- ----------
b                             2
a                             2
c                             1
empty                         0