问题描述
我有一个包含下列数据的表格
列A列B
1 POW
2 POW
1 POWPRO
1 PRO
2 PRO
1 PROUTL
1 TNEUTL
1 UTL
1 UTLTNE
我需要输出如下
输出
列A列B
1,2 POW,POWPRO,PRO,PROUTL,TNEUTL,UTL,UTLTNE
我试过下面的查询。但输出是不同的。
select dbms_lob.substr(ltrim(REGEXP_REPLACE(REPLACE(
REPLACE(
XMLAGG(
XMLELEMENT(A,COULUMN_A)
ORDER BY COULUMN_A).getClobVal(),
'&'A',','),
'< / A& ''),'([^,] +)(,\ 1)+','\1'),
dbms_lob.substr(ltrex(REGEXP_REPLACE(
REPLACE $ b XMLAGG(
XMLELEMENT(A,COULUMN_B)
ORDER BY COULUMN_B).getClobVal(),
'&A',','),
' < / A>',''),'([^,] +)(,\ 1)+','\1')
列A列B
1,2 POW,POWPRO,PROUTL,TNEUTL,UTLTNE
我只想使用regexp_replace
解决方案
我将开始假设你的实际列名是
。A
和B
,而不是A栏
c $ c>Column B
您想要
LIST_AGG
(可用)对于Oracle 11g及更高版本):SELECT
LISTAGG(A,',') )AS A_VALUES
FROM TABLE_NAME;
不要被
WITHIN GROUP
位。这是一个分析函数,它只是Oracle的名称,用于其他DB调用窗口函数。分析/窗口函数的基本思想是它可以访问相邻结果行中的数据,以确定当前行的值。一个简单的例子是一个累积和。
在这种情况下,它的分析/窗口是多余的。我们只是使用它来聚合。我们必须提供
WITHIN GROUP
部分,以防止语法错误。我们告诉它如何排序列表,在这种情况下,它只是我们聚合的列。你可以做更多的分析/窗口函数,但这不是去的地方去这些能力。
事情变得有点复杂,因为你想要
DISTINCT
值:SELECT
LISTAGG ,')WITHIN GROUP(ORDER BY A)AS A_VALUES
FROM(
SELECT DISTINCT A
FROM TABLE_NAME
);
更复杂,因为您需要两个列:
SELECT *
FROM(
SELECT
LISTAGG(A,',')WITHIN GROUP(ORDER BY A)AS A_VALUES
FROM(
SELECT DISTINCT A
FROM TABLE_NAME
)
)A_VALS,
(
SELECT
LISTAGG(B,',') WITHIN GROUP(ORDER BY B)AS B_VALUES
FROM(
SELECT DISTINCT B
FROM TABLE_NAME
)
)B_VALS
这最后一个给你回来你想要的。它只是从子查询中每个列的不同值创建一个字符串,然后执行完全连接(逗号,因为没有过滤器)将两列放在一起。每个子查询返回单个行,因此您只能在最终结果中获取一行。
I have a table with data like below
Column A Column B 1 POW 2 POW 1 POWPRO 1 PRO 2 PRO 1 PROUTL 1 TNEUTL 1 UTL 1 UTLTNE
And I need output like below
Output Column A Column B 1,2 POW,POWPRO,PRO,PROUTL,TNEUTL,UTL,UTLTNE
I tried below query. But the output is different.
select dbms_lob.substr( ltrim(REGEXP_REPLACE(REPLACE( REPLACE( XMLAGG( XMLELEMENT("A",COULUMN_A ) ORDER BY COULUMN_A).getClobVal(), '<A>',','), '</A>',' '),'([^,]+)(,\1)+', '\1'), dbms_lob.substr( ltrim(REGEXP_REPLACE(REPLACE( REPLACE( XMLAGG( XMLELEMENT("A",COULUMN_B ) ORDER BY COULUMN_B).getClobVal(), '<A>',','), '</A>',' '),'([^,]+)(,\1)+', '\1') from table_name
But the output is
Column A Column B 1,2 POW ,POWPRO ,PROUTL ,TNEUTL ,UTLTNE
I want to use only the regexp_replace to search the pattern. Please help me out.
解决方案I'm going to start by assuming that your actual column names are
A
andB
, rather than"Column A"
and"Column B"
. If that assumption is wrong, all you need to do is change the names below.You want
LIST_AGG
(available for Oracle 11g and higher):SELECT LISTAGG(A, ',') WITHIN GROUP (ORDER BY A) AS A_VALUES FROM TABLE_NAME;
Don't be thrown by the
WITHIN GROUP
bit. This is an "analytic function," which is just Oracle's name for what other DBs call window functions. The basic idea of an analytic/window function is that it lets you access data in neighboring result row to determine a value for the current row. A simple example of what they're good for is a cumulative sum.In this case, the fact that it's analytic/window is superfluous. We're just using it to aggregate. We have to provide the
WITHIN GROUP
part, though, to prevent a syntax error. We tell it what to order the list by, which in this case is just the column we're aggregating. You can do a lot more with analytic/window functions, but this isn't the place to go over those capabilities.Things get a little more complicated since you want the
DISTINCT
values:SELECT LISTAGG(A, ',') WITHIN GROUP (ORDER BY A) AS A_VALUES FROM ( SELECT DISTINCT A FROM TABLE_NAME );
And even more complicated since you want both columns:
SELECT * FROM ( SELECT LISTAGG(A, ',') WITHIN GROUP (ORDER BY A) AS A_VALUES FROM ( SELECT DISTINCT A FROM TABLE_NAME ) ) A_VALS, ( SELECT LISTAGG(B, ',') WITHIN GROUP (ORDER BY B) AS B_VALUES FROM ( SELECT DISTINCT B FROM TABLE_NAME ) ) B_VALS
This last one gives you back what you wanted. It just creates a string from the distinct values for each column in a subquery, and then it does a full join (the comma, since there are no filters) to put the two columns together. Each subquery gives back on a single row, so you'll only get one row in the final result.
这篇关于使用oracle中的REGEXP_REPLACE区分CSV值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!