以下是我从网上看到的,碰巧我昨天开发用到了,不知道效果是不是你想要得! SELECT t1.table_name, substr(MAX(sys_connect_by_path(t1.column_name, ';')), 2) column_name --分号分隔 FROM (SELECT a.table_name, a.column_name, row_number() over(PARTITION BY a.table_name ORDER BY a.column_id) rn --column_id rn FROM user_tab_cols a --order by table_name,column_id where a.table_name='TABLE_NAME' ) t1 START WITH t1.rn = 1 CONNECT BY t1.table_name = PRIOR t1.table_name AND t1.rn - 1 = PRIOR t1.rn GROUP BY t1.
能不能解释下max+decode的用法!结合参数!``
select id ,decode(rn,1,name1,null)1, decode(rn,2,name2,null), decode(rn,3,name3,null) from ( select a.*,row_number() over (partition by a.id order by a.rowid) rn from pbl a ) group by id这样不行吗``~?
PS:本人也经常忘记,^@^http://topic.csdn.net/u/20080505/11/a0958b42-d938-465f-972a-0f61a2969c97.html?seed=491226048
固定列,可以用max+decode实现
你这需要先生成每个分组里每行数据编号
select id ,max(decode(rn,1,name,null)) name1,
max(decode(rn,2,name,null)) name2,
max(decode(rn,3,name,null)) name3
from
(
select a.*,row_number() over (partition by a.id order by a.rowid) rn
from pbl a
)
group by id
http://topic.csdn.net/u/20080416/11/910e40c1-60f1-441f-8b0f-19a969d30f77.html
SELECT t1.table_name,
substr(MAX(sys_connect_by_path(t1.column_name, ';')), 2) column_name --分号分隔
FROM (SELECT a.table_name,
a.column_name,
row_number() over(PARTITION BY a.table_name ORDER BY a.column_id) rn
--column_id rn
FROM user_tab_cols a
--order by table_name,column_id
where a.table_name='TABLE_NAME'
) t1
START WITH t1.rn = 1
CONNECT BY t1.table_name = PRIOR t1.table_name
AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.
decode(rn,2,name2,null),
decode(rn,3,name3,null)
from (
select a.*,row_number() over (partition by a.id order by a.rowid) rn
from pbl a
)
group by id这样不行吗``~?