Oracle:归一化为CSV字符串的字段 [英] Oracle: normalized fields to CSV string
问题描述
我有一些这样的归一化数据.
I have some one-many normalized data that looks like this.
a | x
a | y
a | z
b | i
b | j
b | k
哪个查询将返回数据,以便将许多"面表示为CSV字符串?
What query will return the data such that the "many" side is represented as a CSV string?
a | x,y,z
b | i,j,k
推荐答案
标记,
如果您使用的是11gR2版本,但不是:-),则可以使用listagg
If you are on version 11gR2, and who isn't :-), then you can use listagg
SQL> create table t (col1,col2)
2 as
3 select 'a', 'x' from dual union all
4 select 'a', 'y' from dual union all
5 select 'a', 'z' from dual union all
6 select 'b', 'i' from dual union all
7 select 'b', 'j' from dual union all
8 select 'b', 'k' from dual
9 /
Tabel is aangemaakt.
SQL> select col1
2 , listagg(col2,',') within group (order by col2) col2s
3 from t
4 group by col1
5 /
COL1 COL2S
----- ----------
a x,y,z
b i,j,k
2 rijen zijn geselecteerd.
如果您的版本不是11gR2,但高于10gR1,那么我建议为此使用模型子句,如下所示:
If your version is not 11gR2, but higher than 10gR1, then I recommend using the model clause for this, as written here: http://rwijk.blogspot.com/2008/05/string-aggregation-with-model-clause.html
如果低于10,则可以在rexem到oracle-base页面的链接中,或在上述博文中的OTN线程的链接中看到几种技术.
If lower than 10, then you can see several techniques in rexem's link to the oracle-base page, or in the link to the OTN-thread in the blogpost mentioned above.
关于, 罗布.
这篇关于Oracle:归一化为CSV字符串的字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!