| SQL Question [message #6527] |
Wed, 23 April 2003 07:31  |
Vidya
Messages: 12
Registered: May 2001
|
Junior Member |
|
|
Hi,
I would like to write a query that concatenates the 2 or more rows of a table.
For Eg.
create table a
(no number(2),name varchar2(10))
The data in a is
select * from a
NO NAME
1 vv
1 vi
2 aa
2 bb
I need to write a query that does the following
NO NAME
1 vv,vi
2 aa,bb
Thanks & regards,
Vidya
|
|
|
|
|
|
| Re: SQL Question [message #6531 is a reply to message #6527] |
Wed, 23 April 2003 10:45  |
Scott Mackey
Messages: 46
Registered: February 2003
|
Member |
|
|
As you will see, Kishore's solution will not work. If you know you are only going to have two names for each number, you can do something like:
SELECT a.no, a.name, b.name
FROM your_table a,
your_table b
WHERE a.no = b.no
AND a.name < b.name
You cannot, however, make a general query to handle any number of different names with the same number. At the very best, you can write a PL/SQL procedure to provide the values.
|
|
|
|
| use pl/sql function [message #6540 is a reply to message #6527] |
Wed, 23 April 2003 20:10 |
nyfor
Messages: 21
Registered: March 2003
|
Junior Member |
|
|
create type tab_strs as table of varchar2(10);
/
create function join_strs(p_tab tab_strs) return varchar2 as
str varchar2(4000);
begin
for i in 1..p_tab.count-1 loop
str := str || p_tab(i) || ',';
end loop;
str := str || p_tab(p_tab(p_tab.count));
return str;
end;
/
select out.no,join_strs(cast(multiset(select name from a where no = out.no)) name
from a out
group by out.no;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
