oracle pl/sql results into one string
Using SQL Fiddle:
select LISTAGG(name, ',') WITHIN GROUP (ORDER BY 1) AS names
from temp_table
Concatenate results from a SQL query in Oracle
-- Oracle 10g --
SELECT deptno, WM_CONCAT(ename) AS employees
FROM scott.emp
GROUP BY deptno;
Output:
10 CLARK,MILLER,KING
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD
How can multiple rows be concatenated into one in Oracle without creating a stored procedure?
There are many way to do the string aggregation, but the easiest is a user defined function. Try this for a way that does not require a function. As a note, there is no simple way without the function.
This is the shortest route without a custom function: (it uses the ROW_NUMBER() and SYS_CONNECT_BY_PATH functions )
SELECT questionid,
LTRIM(MAX(SYS_CONNECT_BY_PATH(elementid,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS elements
FROM (SELECT questionid,
elementid,
ROW_NUMBER() OVER (PARTITION BY questionid ORDER BY elementid) AS curr,
ROW_NUMBER() OVER (PARTITION BY questionid ORDER BY elementid) -1 AS prev
FROM emp)
GROUP BY questionid
CONNECT BY prev = PRIOR curr AND questionid = PRIOR questionid
START WITH curr = 1;
PL/SQL: how to combine rows into one string
A stored procedure in Oracle consists of a declaration part and an execution part. You use the DECLARE section for declaring variables and the block from BEGIN to END for the program. Use SELECT INTO to select a value into your variable.
DECLARE
v_str varchar(10000);
BEGIN
select
listagg(filename || ',' || priority, ',') within group (order by priority)
into v_str
from table_name;
...
END;
String concatenation select query is not giving result pl/sql script
You'll need dynamic SQL for that.
SQL> DECLARE
2 person_id NUMBER;
3 age_where VARCHAR2 (100 CHAR);
4 TEMP_WHERE VARCHAR2 (100 CHAR) := '';
5 add_temp_where BOOLEAN := TRUE;
6 l_str VARCHAR2 (400);
7 BEGIN
8 age_where := q'[ and age=28]';
9
10 IF (ADD_TEMP_WHERE)
11 THEN
12 TEMP_WHERE := age_where;
13 END IF;
14
15 l_str := q'[SELECT id FROM PERSON WHERE name = 'David']' || TEMP_WHERE;
16
17 EXECUTE IMMEDIATE l_str
18 INTO person_id;
19
20 DBMS_OUTPUT.PUT_LINE ('result : ' || person_id);
21 END;
22 /
result : 1
PL/SQL procedure successfully completed.
SQL>
SQL Query to concatenate column values from multiple rows in Oracle
There are a few ways depending on what version you have - see the oracle documentation on string aggregation techniques. A very common one is to use LISTAGG:
SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS description
FROM B GROUP BY pid;
Then join to A to pick out the pids you want.
Note: Out of the box, LISTAGG only works correctly with VARCHAR2 columns.
How can I combine multiple rows into a comma-delimited list in Oracle?
Here is a simple way without stragg or creating a function.
create table countries ( country_name varchar2 (100));
insert into countries values ('Albania');
insert into countries values ('Andorra');
insert into countries values ('Antigua');
SELECT SUBSTR (SYS_CONNECT_BY_PATH (country_name , ','), 2) csv
FROM (SELECT country_name , ROW_NUMBER () OVER (ORDER BY country_name ) rn,
COUNT (*) OVER () cnt
FROM countries)
WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;
CSV
--------------------------
Albania,Andorra,Antigua
1 row selected.
As others have mentioned, if you are on 11g R2 or greater, you can now use listagg which is much simpler.
select listagg(country_name,', ') within group(order by country_name) csv
from countries;
CSV
--------------------------
Albania, Andorra, Antigua
1 row selected.
Dynamically concatenating strings using a loop in Oracle PL/SQL
A posible solution:
Declare
/* Dynamically concatenating strings using a loop in Oracle PL/SQL */
--
type tyArray_parsed
is table of varchar2(2000)
index by binary_integer;
--
arAddress_parsed tyArray_parsed;
--
cnuSTREET_TYPE constant integer:=1;
cnuSTREET_NAME constant integer:=2;
cnuHOUSE_NUMBER constant integer:=3;
cnuREFERENCE constant integer:=4;
--
Procedure print(p_isbTexto varchar2)
is
nuIzq_print integer;
nuDer_print integer;
Begin
nuDer_print:=0;
Loop
nuIzq_print:=nuDer_print+1;
nuDer_print:=instr(p_isbTexto,chr(10),nuIzq_print);
exit when nvl(nuDer_print,0)=0;
dbms_output.put_line(substr(p_isbTexto, nuIzq_print, nuDer_print-nuIzq_print));
End loop;
dbms_output.put_line(substr(p_isbTexto, nuIzq_print));
End print;
--
procedure pAddress_split(isbAddress_string varchar2,
oarAddress_parsed out tyArray_parsed
)
is
type tyArray_string
is table of varchar2(2000)
index by binary_integer;
--
arFields tyArray_string;
nuLeft_pos integer;
nuRight_pos integer;
nuMy_number number;
blIs_number boolean;
nuStep integer;
Begin
print('---------------------------------------');
print('isbAddress_string=['||isbAddress_string||']');
--
nuRight_pos:=0;
Loop
nuLeft_pos:=nuRight_pos+1;
nuRight_pos:=instr(isbAddress_string,' ',nuLeft_pos);
exit when nvl(nuRight_pos,0)=0;
arFields(arFields.COUNT+1):=substr(isbAddress_string,
nuLeft_pos,
nuRight_pos-nuLeft_pos
);
End loop;
arFields(arFields.COUNT+1):=substr(isbAddress_string,
nuLeft_pos
);
print('arFields.COUNT='||arFields.COUNT);
--
For nuI in arFields.FIRST..arFields.LAST loop
print(lpad(nuI,4)||'['||arFields(nui)||']');
End loop;
--
nuStep:=cnuSTREET_TYPE;
For nuI in arFields.FIRST..arFields.LAST loop
if nuStep=cnuSTREET_TYPE then
oarAddress_parsed(cnuSTREET_TYPE):=arFields(nui);
nuStep:=cnuSTREET_NAME;
Elsif nuStep=cnuSTREET_NAME then
blIs_number:=false;
Begin
nuMy_number:=to_number(arFields(nui));
blIs_number:=true;
Exception
when others then
blIs_number:=false;
End;
if blIs_number then
oarAddress_parsed(cnuHOUSE_NUMBER):=arFields(nui);
nuStep:=cnuREFERENCE;
Else
if not(oarAddress_parsed.exists(cnuSTREET_NAME)) then
oarAddress_parsed(cnuSTREET_NAME):=arFields(nui);
Else
oarAddress_parsed(cnuSTREET_NAME):=oarAddress_parsed(cnuSTREET_NAME)||' '||arFields(nui);
End if;
End if;
Elsif nuStep=cnuREFERENCE then
if not(oarAddress_parsed.exists(cnuREFERENCE)) then
oarAddress_parsed(cnuREFERENCE):=arFields(nui);
Else
if arFields(nui)=',' then
oarAddress_parsed(cnuREFERENCE):=oarAddress_parsed(cnuREFERENCE)||arFields(nui);
Else
oarAddress_parsed(cnuREFERENCE):=oarAddress_parsed(cnuREFERENCE)||' '||arFields(nui);
end if;
End if;
End if;
End loop;
--
for nuI in oarAddress_parsed.FIRST..oarAddress_parsed.LAST loop
print(lpad(nuI,2)||'|['||oarAddress_parsed(nui)||']');
End loop;
End pAddress_split;
Begin
dbms_application_info.set_module('Split','START-'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
--
pAddress_split('Avenida Doutor Theomário Pinto da Costa 450 Condominio Renaissance, rua 1, casa 1',arAddress_parsed);
pAddress_split('Rua Álvaro Peres Filho 60 Casa azul em frente ao orelhão',arAddress_parsed);
pAddress_split('Travessa Delegado Zé Lima 61 antiga Praça Rio Branco',arAddress_parsed);
pAddress_split('Rua Finlândia 28 Qd 111',arAddress_parsed);
pAddress_split('Alameda Áustria 107 Condomínio Jardim Europa I',arAddress_parsed);
--
--
dbms_application_info.set_module('Split','END-'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
End;
/
Answer:
---------------------------------------
isbAddress_string=[Avenida Doutor Theomário Pinto da Costa 450 Condominio Renaissance, rua 1, casa 1]
arFields.COUNT=13
1[Avenida]
2[Doutor]
3[Theomário]
4[Pinto]
5[da]
6[Costa]
7[450]
8[Condominio]
9[Renaissance,]
10[rua]
11[1,]
12[casa]
13[1]
1|[Avenida]
2|[Doutor Theomário Pinto da Costa]
3|[450]
4|[Condominio Renaissance, rua 1, casa 1]
---------------------------------------
isbAddress_string=[Rua Álvaro Peres Filho 60 Casa azul em frente ao orelhão]
arFields.COUNT=11
1[Rua]
2[Álvaro]
3[Peres]
4[Filho]
5[60]
6[Casa]
7[azul]
8[em]
9[frente]
10[ao]
11[orelhão]
1|[Rua]
2|[Álvaro Peres Filho]
3|[60]
4|[Casa azul em frente ao orelhão]
---------------------------------------
isbAddress_string=[Travessa Delegado Zé Lima 61 antiga Praça Rio Branco]
arFields.COUNT=9
1[Travessa]
2[Delegado]
3[Zé]
4[Lima]
5[61]
6[antiga]
7[Praça]
8[Rio]
9[Branco]
1|[Travessa]
2|[Delegado Zé Lima]
3|[61]
4|[antiga Praça Rio Branco]
---------------------------------------
isbAddress_string=[Rua Finlândia 28 Qd 111]
arFields.COUNT=5
1[Rua]
2[Finlândia]
3[28]
4[Qd]
5[111]
1|[Rua]
2|[Finlândia]
3|[28]
4|[Qd 111]
---------------------------------------
isbAddress_string=[Alameda Áustria 107 Condomínio Jardim Europa I]
arFields.COUNT=7
1[Alameda]
2[Áustria]
3[107]
4[Condomínio]
5[Jardim]
6[Europa]
7[I]
1|[Alameda]
2|[Áustria]
3|[107]
4|[Condomínio Jardim Europa I]
How to concatenate strings in oracle pl/sql loop?
You can consecutively use REGEXP_SUBSTR() (in order to determine each substring delimited by semi-colons), and INSTR() (in order to determine the substrings with or without % character). Then combine the string trough use of LISTAGG() such as
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
Pvalue1 VARCHAR2(100):='a;b;c;e%;f%;h%;';
v1 VARCHAR2(100);
v2 VARCHAR2(100);
BEGIN
WITH t AS
(
SELECT REGEXP_SUBSTR( Pvalue1, '[^;]+', 1, level ) AS piece, level AS lvl
FROM dual
CONNECT BY level <= REGEXP_COUNT( Pvalue1, ';' )
)
SELECT LISTAGG(CASE WHEN INSTR( piece,'%')=0 THEN ''''||piece||'''' END,';') WITHIN GROUP (ORDER BY lvl) AS v1,
LISTAGG(CASE WHEN INSTR( piece,'%')>0 THEN piece END) WITHIN GROUP (ORDER BY lvl) AS v2
INTO v1, v2
FROM t;
DBMS_OUTPUT.PUT_LINE( 'v1 : '||v1 );
DBMS_OUTPUT.PUT_LINE( 'v2 : '||v2 );
END;
/
v1 : 'a';'b';'c'
v2 : e%f%h%
Related Topics
In SQL, What Is the Letter After a Table Name in a Select Statement
Oracle Equivalent of Rowlock, Updlock, Readpast Query Hints
Local Collection Types Not Allowed in SQL Statements
Cross Table Dependency/Constraint in SQL Database
Convert Delimited String to Rows in Oracle
Nhibernate Count Distinct (Based on Multiple Columns)
Openrowset and Opendataset Without Sysadmin Rights
Order Guarantee for Identity Assignment in Multi-Row Insert in SQL Server
Inserting Default Value as Current Date + 30 Days in MySQL
Do Clustered Index on a Column Guarantees Returning Sorted Rows According to That Column
Oracle as Keyword and Subqueries
Hive - How to Further Optimize a Hiveql Query
Is This Normalization Correct? (Two Many-To-Manys Connected by a Many-To-One)
Typo3: SQL Error: 'Incorrect Integer Value: '' for Column 'Sys_Language_Uid' at Row 1'