oracle9i,10g行列转换
1、oracle10g下行列转换：select id,wm_concat(A) name from test3 group by id;
2、oracle9i行列转换：
test表的数据结构
idA
1a
1b
1c
2aa
2bb
3aaa
4aaaa
sql:
with test1 as (select t.* from test t)
SELECT id,substr(max(sys_connect_by_path(A,',')),2)
FROM (SELECT id,A,rn, LEAD(rn) OVER(PARTITION BY id ORDER BY rn desc) rn1
FROM (SELECT id,A,ROW_NUMBER() OVER(ORDER BY id) rn FROM test1))
START WITH rn1 IS NULL
CONNECT BY rn1 = PRIOR rn
GROUP BY id;
一般行列转换都是复杂SQL得到一个数据块，所以这里用with表示了下。
sql分解：
1、
因为sql都是从内到外执行的，所以第一步肯定是：
with test1 as (select t.* from test t)
SELECT id,A,ROW_NUMBER() OVER(ORDER BY id) rn FROM test1;
结果：多加了一个rn的列，和rownum rn差不多
2、运用开窗函数等找出父子关系
with test1 as (select t.* from test t)
SELECT id,A,rn, LEAD(rn) OVER(PARTITION BY id ORDER BY rn desc) rn1
FROM (SELECT id,A,ROW_NUMBER() OVER(ORDER BY id) rn FROM test1);
lead:lead(字段1,n) over ( order by 字段3),向当前行的前n行提取字段1的值.
data:
idArnrn1
1a32
1b21
1c1
2aa54
2bb4
3aaa6
4aaaa7
这个结果集的rn和rn1列貌似具有父子关系？
3、start with ... connect by prior...
start with:开始遍历的节点
connect by prior:指定节点的父子关系
观察第二步得到的数据，以ID分组的话RN1始终有一个为NULL，rn则是1,2,3...，
so:start with rn1 is null 是必须的。
sys_connect_by_path这个函数是oracle9i出来的。
能对树形结构的某个字段(树枝)进行连接,so 连完后max下在substr等扫尾工作就OK啦。
如果想让多列出数据转行就多加几个substr(max(sys_connect_by_path(A,',')),2)就行。
页:
[1]