oracle行列互换
来源:互联网 发布:随身带着淘宝去异界301 编辑:程序博客网 时间:2024/05/01 19:52
http://blog.csdn.net/you_tube/article/details/4059251
--1
CREATE TABLE t_row_str(ID INT,
col VARCHAR2(10));
--2
INSERT INTO t_row_str VALUES(1,'a');
INSERT INTO t_row_str VALUES(1,'b');
INSERT INTO t_row_str VALUES(1,'c');
INSERT INTO t_row_str VALUES(2,'a');
INSERT INTO t_row_str VALUES(2,'d');
INSERT INTO t_row_str VALUES(2,'e');
INSERT INTO t_row_str VALUES(3,'c');
--3
--5.1
--MAX + DECODE
--适用范围:8i,9i,10g及以后版本
SELECT id,
MAX(decode(rn, 1, col, NULL)) ||
MAX(decode(rn, 2, ',' || col, NULL)) ||
MAX(decode(rn, 3, ',' || col, NULL)) str
FROM (SELECT id,
col,
row_number() over(PARTITION BY id ORDER BY col) AS rn
FROM t_row_str) t
GROUP BY id
ORDER BY 1;
--5.2
ROW_NUMBER + LEAD
适用范围:8i,9i,10g及以后版本
SELECT id, str
FROM (SELECT id,
row_number() over(PARTITION BY id ORDER BY col) AS rn,
col || lead(',' || col, 1) over(PARTITION BY id ORDER BY col) ||
lead(',' || col, 2) over(PARTITION BY id ORDER BY col) ||
lead(',' || col, 3) over(PARTITION BY id ORDER BY col) AS str
FROM t_row_str)
WHERE rn = 1
ORDER BY 1;
--5.3
MODEL
适用范围:10g及以后版本
SELECT id, substr(str, 2) str FROM t_row_str
MODEL
RETURN UPDATED ROWS
PARTITION BY(ID)
DIMENSION BY(row_number() over(PARTITION BY ID ORDER BY col) AS rn)
MEASURES (CAST(col AS VARCHAR2(20)) AS str)
RULES UPSERT
ITERATE(3) UNTIL( presentv(str[iteration_number+2],1,0)=0)
(str[0] = str[0] || ',' || str[iteration_number+1])
ORDER BY 1;
--5.4
SYS_CONNECT_BY_PATH
--适用范围:8i,9i,10g及以后版本
SELECT t.id id, MAX(substr(sys_connect_by_path(t.col, ','), 2)) str
FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
FROM t_row_str) t
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND id = PRIOR id
GROUP BY t.id;
--适用范围:10g及以后版本
SELECT t.id id, substr(sys_connect_by_path(t.col, ','), 2) str
FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
FROM t_row_str) t
WHERE connect_by_isleaf = 1
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND id = PRIOR id;
--5.5
WMSYS.WM_CONCAT
适用范围:10g及以后版本
这个函数预定义按','分隔字符串,若要用其他符号分隔可以用,replace将','替换。
SELECT id, REPLACE(wmsys.wm_concat(col), ',', '/') str
FROM t_row_str
GROUP BY id;
- Oracle行列互换总结
- oracle行列互换
- Oracle行列互换
- Oracle 行列互换
- Oracle行列互换
- Oracle行列互换
- oracle行列互换
- 行列互换
- 行列互换
- 行列互换
- 行列互换
- 行列互换
- 行列互换
- oracle 动态交叉查询 行列互换 oracle动态交叉表
- Oracle行列互换 横表和纵表
- Oracle 11g 行列互换 pivot 和 unpivot 说明
- Oracle 11g 行列互换 pivot 和 unpivot 说明
- Oracle 11g 行列互换 pivot 和 unpivot 说明
- 存储器管理——连续分配方式
- 设为首页、添加到收藏夹、更改地址栏前小图标、按下回车键事件、关闭窗口
- memcache linux 下的使用
- 设置控件背景色
- 远程调试wcf服务
- oracle行列互换
- ubifs入门介绍
- 教你如何迅速秒杀掉:99%的海量数据处理面试题
- 使用HISTTIMEFORMAT 显示history命令的时间戳
- 安装smarty
- PyGTK 多线程
- 不作恶已成历史?
- webservice 测试窗体只能用于来自本地计算机的请求
- Speex on Android