【转】合并行记录
来源:互联网 发布:kvm网络配置 编辑:程序博客网 时间:2024/05/17 03:43
- 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 cName pName
1 百度 研发部,市场部
2 Google 研发部
3 网易 研发部,平台架构
WMSYS.WM_CONCAT: 依赖WMSYS 用户,不同oracle环境时可能用不了,返回类型为CLOB,可用substr截取长度后to_char转化为字符类型
LISTAGG : 11g2才提供的函数,不支持distinct,拼接长度不能大于4000,函数返回为varchar2类型,最大长度为4000.
方法一:使用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;
方法二:使用listagg()
SELECT ListAgg(pname,'/') WITHIN GROUP (ORDER BY t1.ID) 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;
- 【转】合并行记录
- 合并行
- oracle数据库合并行记录,WMSYS.WM_CONCAT函数的用法
- 读文件合并行
- 删除合并行
- 合并行数据
- GridView合并行
- Oracle合并行 解析
- 合并行并排序
- GridView 合并行、列
- mysql 合并行
- SQL 合并行
- winForm 合并行处理
- EXT gridpanel 合并行
- sqlserver 合并行
- ireport 合并行
- Oracle 拆分合并行
- SQL:行转列、合并行
- ActionView使用
- Light OJ 1257 Farthest Nodes in a Tree (II) (树形DP)
- JDK设置环境变量(classpath)时:dt.jar ,tool.jar,rt.jar的作用
- Android数据存储(二)----PreferenceFragment详解
- 蓝桥杯—FJ的字符串 、Sine之舞 、完美的代价
- 【转】合并行记录
- Setoolkit社会工程学工具包
- 单例模式2
- TabLayout+ViewPager简单使用
- 用户管理系统——结构分析
- 再别康桥
- 解决Listview页脚RemoveFooterView报java.lang.ClassCastException错问题
- 【EasyDarwin】流媒体服务器部署
- 算法训练 筛选号码