如何获得以下结果?
问题描述:
如何获得以下结果?如何获得以下结果?
输入: -
t1
--------------
col1 col2 col3
--------------
101, abc, 100
101, xyz, 200
101, rst, 300
-------------
输出: -
101 abc 100 xyz 200 rst 300
答
请尝试:
SELECT col1, replace(wm_concat(col2||col3),',', '') FROM t1 GROUP BY col1;
或
SELECT col1, (SELECT XMLAGG(xmlelement(X, X1.col2||col3)order by X1.col2).extract('//text()')
FROM t1 X1 WHERE X1.col1=X.col1)
FROM t1 X
答
这适用于11g并维护项目的顺序。
select LISTAGG (code, ' ') WITHIN GROUP (ORDER BY rn, coln)
from(
select code, min(rn) as rn, min(coln) as coln
from(
select col1 as code, rownum rn, 1 as coln from t order by col1, col3
union all
select col2 as code, rownum rn, 2 as coln from t order by col1, col3
union all
select col3 as code, rownum rn, 3 as coln from t order by col1, col3
)
group by code
)
答
尝试用
select col1 || ' ' || listagg(col2 || ' ' || col3, ' ') within group (order by COL3)
from t1
group by col1;
哪个版本的Oracle? – TechDo 2013-03-12 07:10:24
结果的顺序很重要? – 2013-03-12 07:10:58
订单也很重要 – Madhav 2013-03-12 07:13:42