使用WMSYS.WM_CONCAT函数实现行列转换

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 CNAME WMSYS.WM_CONCAT(T2.PNAME)
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://wengr.blog.163.com/blog/static/291961822012222424348/