1500字范文,内容丰富有趣,写作好帮手!
1500字范文 > SQL函数:WMSYS.WM_CONCAT行转列

SQL函数:WMSYS.WM_CONCAT行转列

时间:2024-09-01 03:47:37

相关推荐

SQL函数:WMSYS.WM_CONCAT行转列

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)函数实现:

selectu_id,wmsys.wm_concat(goods||'('||num||'斤)')goods_sum

fromshoppinggroupbyu_id ;

---------------------------------

注意:

在不同的oracle版本中,wmsys.wm_concat 返回的数据类型不一致,10G版本中,返回的是字符串类型,11G版本中,返回的是clob类型。

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

具体参见:/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;

期望结果:

IDcNamepName

1百度研发部,市场部

2Google研发部

3网易研发部,平台架构

方法一:使用wmsys.wm_concat()

SELECT t1.ID,ame,wmsys.wm_concat(t2.pName) FROM tab_name t1,tab_name2 t2 WHERE t1.ID=t2.ID GROUP BY ame,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,ame ORDER BY t1.ID;

本内容不代表本网观点和政治立场,如有侵犯你的权益请联系我们处理。
网友评论
网友评论仅供其表达个人看法,并不表明网站立场。