|
|
| Re: Concatenate Column Values from Multiple Rows into a Single Column [message #9629 is a reply to message #9628] |
Tue, 25 November 2003 01:35   |
Maaher
Messages: 6931
Registered: December 2001
|
Senior Member |
|
|
In pure SQL, it's not possible I think. But you could create your own function like this:SQL> desc t
Name Null? Type
------------------------------- -------- ----
ID NUMBER
VAL NUMBER
SQL> select * from t;
ID VAL
---------- ----------
1 1
1 2
1 3
1 7
2 5
2 8
3 1
3 3
4 1
9 rows selected.
SQL> ed
Wrote file afiedt.buf
1 create or replace function concat_val(p_id in number)
2 return varchar2
3 is
4 cursor c_val
5 is
6 Select val
7 From t
8 Where id = p_id;
9 v_return varchar2(2000);
10 begin
11 for rec in c_val
12 loop
13 v_return := v_return||'-'||rec.val;
14 end loop;
15 v_return := substr(v_return,2);
16 return v_return;
17* end;
SQL> /
Function created.
SQL> Column TEST format a50
SQL> Select Distinct id
2 , concat_val(id) TEST
3 From t;
ID TEST
---------- --------------------------------------------------
1 1-2-3-7
2 5-8
3 1-3
4 1
SQL>
MHE
|
|
|
|
| Re: Concatenate Column Values from Multiple Rows into a Single Column [message #9631 is a reply to message #9628] |
Tue, 25 November 2003 05:04 |
Adrian Billington
Messages: 139
Registered: December 2002
|
Senior Member |
|
|
An alternative method to Maaher's would be to make use of the MULTISET operator. You still require a user-function but unlike Maaher's example, you will be doing it in SQL and won't be context-switching between SQL and PL/SQL. If you like Maaher's method, then make sure you use BULK COLLECT rather than the FOR LOOP to speed it up by orders of magnitude.
The user-function in my example will be used to turn a collection into a string. So you'll also need a collection type.
<pre style="font-family: Courier New, Monospace; color: Black; font-size: 9pt">
SQL> select * from t;
CODE CA
---------- --
1 01
1 02
1 03
2 01
2 02
SQL>
SQL> --
SQL> -- Create a nested table type...
SQL> --
SQL> create or replace type typ_nt_v2_4000
2 as
3 table of varchar2(4000);
4 /
Type created.
SQL>
SQL> --
SQL> -- Create a function to turn a collection into a delimited string...
SQL> --
SQL> create or replace function tbl2str (
2 nt_in in typ_nt_v2_4000,
3 dlm_in in varchar2 default ','
4 ) return varchar2 is
5
6 v_str varchar2(32767);
7 v_dlm varchar2(10);
8
9 begin
10 if nt_in.count > 0 theN
11 for i in nt_in.first .. nt_in.last loop
12 begin
13 v_str := v_str || v_dlm || nt_in(i);
14 v_dlm := dlm_in;
15 exception
16 when no_data_found then
17 null;
18 end;
19 end loop;
20 end if;
21 return v_str;
22 end;
23 /
Function created.
SQL>
SQL> --
SQL> -- Create a collection of categories for each code,
SQL> -- then convert them to a single string
SQL> -- using the tbl2str function...
SQL> --
SQL> col cat_list format a20
SQL>
SQL> select distinct
2 t1.code
3 , tbl2str( cast( multiset( select category
4 from t t2
5 where t1.code = t2.code
6 )
7 as typ_nt_v2_4000 )) as cat_list
8 from t t1;
CODE CAT_LIST
---------- --------------------
1 01,02,03
2 01,02
</pre>
In 9i you can use an aggregate function of your own ( search asktom for "string_agg" ), but the above will work in 8i.
Regards
Adrian
|
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
