1.通过 10g 所提供的 WMSYS.WM_CONCAT 函数即可以完成 行转列的效果
select group_code, wm_concat(display_title) from DR_OPM_DATASET_RELATED
group by group_code
网上看到例子:
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;
SELECT t1.ID,ame,wmsys.wm_concat(t2.pName) FROM tab_name t1,tab_name2 t2 WHERE t1.ID=t2.ID GROUP BY t1.id,ame;
期望结果:
ID cName pName
1 百度 研发部,市场部
2 Google 研发部,平台架构
3 网易 研发部
还有二种方法 也介绍下:
方法二:使用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;
GROUP BY 后面还有二个函数比较常用:
ROLLUP和CUBE 用法
Oracle的GROUP BY语句除了最基本的语法外,还支持ROLLUP和CUBE语句。如果是Group by ROLLUP(A, B, C)的话,首先会对(A、B、C)进行GROUP BY,然后对(A、B)进行GROUP BY,然后是(A)进行GROUP BY,最后对全表进行GROUP BY操作。
如果是GROUP BY CUBE(A, B, C),则首先会对(A、B、C)进行GROUP BY,然后依次是(A、B),(A、C),(A),(B、C),(B),(C),最后对全表进行GROUP BY操作。 grouping_id()可以美化效果。除了使用GROUPING函数,还可以使用GROUPING_ID来标识GROUP BY的结果。
也可以 Group by Rollup(A,(B,C)) ,Group by ARollup(B,C),…… 这样任意按自己想要的形式结合统计数据,非常方便。
Rollup():分组函数可以理解为group by的精简模式,具体分组模式如下:
Rollup(a,b,c): (a,b,c),(a,b),(a),(全表)
Cube():分组函数也是以group by为基础,具体分组模式如下:
cube(a,b,c):(a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),(全表)
引自:/blog/1954176