SQL函数：WMSYS.WM_CONCAT行转列

忘是亡心i 2022-08-06 01:22 135阅读 0赞

### SQL函数：wm\_concat   ###

wm\_concat(column)：此函数实现字段合并，可以把列值以","号分隔起来并显示成一行，实现行转列的效果。

例如：表shopping:

\-----------------------------------------

u\_id       goods            num

\-----------------------------------------

1               苹果               2

2               梨子               5

1               西瓜               4

3               葡萄               1

3               香蕉               1

1               橘子               3

\-----------------------------------------

想要的结果为:

u\_id          goods\_sum

\-----------------------------------------

1              苹果(2斤),西瓜(4斤),橘子(3斤)

2              梨子(5斤)

3              葡萄(1斤),香蕉(1斤)

\---------------------------------

使用oracle的wm\_concat(column)函数实现：

select u\_id, wmsys.wm\_concat(goods || '(' || num || '斤)' ) goods\_sum    
 from shopping  group by u\_id ;

\---------------------------------

**注意：**

**在不同的oracle版本中，wmsys.wm\_concat 返回的数据类型不一致，10G版本中，返回的是字符串类型，11G版本中，返回的是clob类型。**

**如果返回的是字符串类型，则返回是有长度限制的，这时当不满足需求时，需重写该函数，返回clob字段：****首先创建一个Type，一个Type Body，然后利用该Type和Type Body 创建一个返回类型为Clob的function,返回的结果是中间用逗号隔开的clob字段。**

**具体参见：[http://www.cnblogs.com/lowerCaseK/p/wm\_concat\_limit.html][http_www.cnblogs.com_lowerCaseK_p_wm_concat_limit.html]**

## WMSYS.WM\_CONCAT 函數的用法 ##

select t.rank, t.Name from t\_menu\_item t;  
  
10 CLARK  
10 KING  
10 MILLER  
20 ADAMS  
20 FORD  
20 JONES  
20 SCOTT  
20 SMITH  
30 ALLEN  
30 BLAKE  
30 JAMES  
30 MARTIN  
30 TURNER  
30 WARD  
  
\--------------------------------  
我们通过 10g 所提供的 WMSYS.WM\_CONCAT 函数即可以完成 行转列的效果  
  
select t.rank, WMSYS.WM\_CONCAT(t.Name) TIME From t\_menu\_item t GROUP BY t.rank;  
  
DEPTNO ENAME  
\------ ----------  
10 CLARK, KING, MILLER  
20 ADAMS, FORD, JONES, SCOTT, SMITH  
30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

例子如下：

SQL> create table idtable (id number,name varchar2(30));

Table created

SQL> insert into idtable values(10,'ab');

1 row inserted

SQL> insert into idtable values(10,'bc');

1 row inserted

SQL> insert into idtable values(10,'cd');

1 row inserted

SQL> insert into idtable values(20,'hi');

1 row inserted

SQL> insert into idtable values(20,'ij');

1 row inserted  
SQL> insert into idtable values(20,'mn');

1 row inserted

SQL> select \* from idtable;

ID NAME  
\---------- ------------------------------  
10 ab  
10 bc  
10 cd  
20 hi  
20 ij  
20 mn

6 rows selected  
SQL> select id,wmsys.wm\_concat(name) name from idtable  
2 group by id;

ID NAME  
\---------- --------------------------------------------------------------------------------  
10 ab,bc,cd  
20 hi,ij,mn

SQL> select id,wmsys.wm\_concat(name) over (order by id) name from idtable;

ID NAME  
\---------- --------------------------------------------------------------------------------  
10 ab,bc,cd  
10 ab,bc,cd  
10 ab,bc,cd  
20 ab,bc,cd,hi,ij,mn  
20 ab,bc,cd,hi,ij,mn  
20 ab,bc,cd,hi,ij,mn

6 rows selected

SQL> select id,wmsys.wm\_concat(name) over (order by id,name) name from idtable;

ID NAME  
\---------- --------------------------------------------------------------------------------  
10 ab  
10 ab,bc  
10 ab,bc,cd  
20 ab,bc,cd,hi  
20 ab,bc,cd,hi,ij  
20 ab,bc,cd,hi,ij,mn

6 rows selected

个人觉得这个用法比较有趣.

SQL> select id,wmsys.wm\_concat(name) over (partition by id) name from idtable;

ID NAME  
\---------- --------------------------------------------------------------------------------  
10 ab,bc,cd  
10 ab,bc,cd  
10 ab,bc,cd  
20 hi,ij,mn  
20 hi,ij,mn  
20 hi,ij,mn

6 rows selected

SQL> select id,wmsys.wm\_concat(name) over (partition by id,name) name from idtable;

ID NAME  
\---------- --------------------------------------------------------------------------------  
10 ab  
10 bc  
10 cd  
20 hi  
20 ij  
20 mn

6 rows selected

ps:

**wmsys.wm\_concat、sys\_connect\_by\_path、自定义行数实现行列转换：**

CREATE TABLE tab\_name(ID INTEGER NOT NULL PRIMARY KEY,cName VARCHAR2(20));  
CREATE TABLE tab\_name2(ID INTEGER NOT NULL,pName VARCHAR2(20));

INSERT INTO tab\_name(ID,cName) VALUES (1,'百度');  
INSERT INTO tab\_name(ID,cName) VALUES (2,'Google');  
INSERT INTO tab\_name(ID,cName) VALUES (3,'网易');  
INSERT INTO tab\_name2(ID,pName) VALUES (1,'研发部');  
INSERT INTO tab\_name2(ID,pName) VALUES (1,'市场部');  
INSERT INTO tab\_name2(ID,pName) VALUES (2,'研发部');  
INSERT INTO tab\_name2(ID,pName) VALUES (2,'平台架构');  
INSERT INTO tab\_name2(ID,pName) VALUES (3,'研发部');  
COMMIT;

期望结果：

ID cNamepName

1 百度研发部,市场部

2 Google研发部

3 网易研发部,平台架构

方法一:使用wmsys.wm\_concat()

SELECT t1.ID,t1.cName,wmsys.wm\_concat(t2.pName) FROM tab\_name t1,tab\_name2 t2 WHERE t1.ID=t2.ID GROUP BY t1.cName,t1.id;

方法二:使用sys\_connect\_by\_path

select id, cName, ltrim(max(sys\_connect\_by\_path(pName, ',')), ',') from (select row\_number() over(PARTITION by t1.id ORDER by cName) r,t1.\*, t2.pName from tab\_name t1, tab\_name2 t2 where t1.id = t2.id)  
start with r=1 CONNECT by prior r =r-1 and prior id = id group by id ,cName order by id;

方法三:使用自定义函数

create or replace function coltorow(midId INT) RETURN VARCHAR2 is  
Result VARCHAR2(1000);  
begin  
FOR cur IN (SELECT pName FROM tab\_name2 t2 WHERE midId=t2.id) LOOP  
RESULT:=RESULT||cur.pName||',';  
END LOOP;  
RESULT:=rtrim(RESULT,',');  
return(Result);  
end coltorow;

SELECT t1.\*,coltorow(t1.ID) FROM tab\_name t1,tab\_name2 t2 WHERE t1.ID=t2.ID GROUP BY t1.ID,t1.cname ORDER BY t1.ID;

[http_www.cnblogs.com_lowerCaseK_p_wm_concat_limit.html]: http://www.cnblogs.com/lowerCaseK/p/wm_concat_limit.html