Home » SQL & PL/SQL » SQL & PL/SQL » SQL use, one column row string value combine (Oracle 10g)
SQL use, one column row string value combine [message #398497] Thu, 16 April 2009 08:22 Go to next message
darmen
Messages: 4
Registered: April 2009
Junior Member
Hi!

I need to combine (concatenate) several row string value of one column into one row and one column.

For expamle:

Select id2, risk from risk_t;

Result:
1 aaaaa
1 bbbbb

But I want to get:
1 aaaa/bbbb

Plz, help.

P.S.
Of course,
I could use a cursor for that, but I want to know, if it's could be done by sql
Re: SQL use, one column row string value combine [message #398500 is a reply to message #398497] Thu, 16 April 2009 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at CONNECT BY and SYS_CONNECT_BY_PATH function (among many other solutions you can find if you search for "pivot").

Regards
Michel

[Updated on: Thu, 16 April 2009 08:29]

Report message to a moderator

Re: SQL use, one column row string value combine [message #398503 is a reply to message #398500] Thu, 16 April 2009 08:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could use Tom Kyte's STRAGG function
Re: SQL use, one column row string value combine [message #398506 is a reply to message #398503] Thu, 16 April 2009 08:40 Go to previous messageGo to next message
darmen
Messages: 4
Registered: April 2009
Junior Member
Tnx, this STRAGG funk. ir great )))

[Updated on: Thu, 16 April 2009 08:40]

Report message to a moderator

Re: SQL use, one column row string value combine [message #398541 is a reply to message #398497] Thu, 16 April 2009 10:43 Go to previous messageGo to next message
yugi.adapala
Messages: 10
Registered: April 2009
Junior Member
if you are on 10g, you can try this. let me know if you have any questions

TYPE tst_array IS TABLE OF VARCHAR2(227);

PROCEDURE make_list(i_values IN tst_array,
o_var OUT NOCOPY VARCHAR2)
IS
lv_str VARCHAR2(32000);
BEGIN

FOR i IN 1.. i_values.COUNT
LOOP
BEGIN
IF (i > 1) THEN
lv_str := lv_str || ', ' || i_values(i);
ELSE
lv_str := i_values(i);
END IF;
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
END LOOP;
o_var := lv_str;
END make_list;

DECLARE


lv_arr tst_array;

lv_bol BINARY_INTEGER;
lv_var VARCHAR2(32000);

BEGIN

SELECT make_list(CAST(COLLECT(t.risk) AS tst_array), lv_var)
FROM risk_t t
WHERE t.num_val_01 = 1;

dbms_output.put_line(lv_var);

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);

END;
Re: SQL use, one column row string value combine [message #398542 is a reply to message #398541] Thu, 16 April 2009 10:48 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
yugi.adapala wrote on Thu, 16 April 2009 16:43
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);




don't do that - it just causes problems.
Re: SQL use, one column row string value combine [message #398656 is a reply to message #398497] Fri, 17 April 2009 01:41 Go to previous messageGo to next message
darmen
Messages: 4
Registered: April 2009
Junior Member
If I make such query, I have error 'CONNECT BY loop in user data'

select id2, SYS_CONNECT_BY_PATH(risk, '/') as risk
from risk_t
connect by prior id2=id2

How could I avoid this error?
Re: SQL use, one column row string value combine [message #398659 is a reply to message #398656] Fri, 17 April 2009 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use row_number function to number the rows for each id.

Regards
Michel
Re: SQL use, one column row string value combine [message #398695 is a reply to message #398659] Fri, 17 April 2009 02:41 Go to previous message
darmen
Messages: 4
Registered: April 2009
Junior Member
tnx, I've got it


Previous Topic: to update mulitple columns based on the multiple column values from other table
Next Topic: UTL_FILE
Goto Forum:
  


Current Time: Wed Dec 07 20:06:43 CST 2016

Total time taken to generate the page: 0.12125 seconds