Home » SQL & PL/SQL » SQL & PL/SQL » Required urgent solution
Required urgent solution [message #193697] Tue, 19 September 2006 01:06 Go to next message
jaydeep mitra
Messages: 20
Registered: August 2006
Location: India
Junior Member
Suppose in my table i have columns SRL_NO ,NAME and it have data as follows

SRL_NO NAME

------- ----------

1 samarjit

1 pattnaik

1 Mr.

2 sachin

2 Tendulkar

2 Mr.

3 Richard

3 Hardlee

3 Sir.

I want an Sql query which will display data as follows :

SRL_NO NAME

------- ----------

1 Mr. Samarjit Pattnaik

2 Mr. Sachin Tendulkar

3 Sir. Richard Hardlee





Thanks & Regards

Jaydeep
Re: Required urgent solution [message #193711 is a reply to message #193697] Tue, 19 September 2006 02:01 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
Maybe this will help (thanks to T. Kyte).
Re: Required urgent solution [message #193725 is a reply to message #193697] Tue, 19 September 2006 02:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, unless you've got another column to tell you what order these three fields go in, you've got a problem, as you can't guarantee getting the fields back in the right order.

Normaly I'd have said
Quote:

Search for Pivot Table on this site

but I tried that myself, and it's a bit tricky to actually find the solution hidden amongst all the other instruction to look for Pivot Table, so here's how to do it. Note that I've added a sub_id column to allow me to order the name fields correctly:

create table temp_name (id  number, sub_id number, txt varchar2(30));

insert into temp_name values(1,1, 'samarjit');
insert into temp_name values(1,2, 'pattnaik');
insert into temp_name values(1,3, 'Mr.');
insert into temp_name values(2,1, 'sachin');
insert into temp_name values(2,2, 'Tendulkar');
insert into temp_name values(2,3, 'Mr.');
insert into temp_name values(3,1, 'Richard');
insert into temp_name values(3,2, 'Hardlee');
insert into temp_name values(3,3, 'Sir.');

select id
      ,max(decode(sub_id,3,txt||' ',null))
      ||max(decode(sub_id,2,txt||' ',null))
      ||max(decode(sub_id,1,txt,null))
from   temp_name
group by id;

"ID"	"NAME"
"1"	"Mr. pattnaik samarjit"
"2"	"Mr. Tendulkar sachin"
"3"	"Sir. Hardlee Richard"

I have to say that my advice would be to scrap this data structure entirely.
Re: Required urgent solution [message #194925 is a reply to message #193725] Tue, 26 September 2006 03:35 Go to previous messageGo to next message
jaydeep mitra
Messages: 20
Registered: August 2006
Location: India
Junior Member
Thanks to all of you for your answers.
I had also found a solution to this problem of mine

select t1.sl,t3.name||t2.name||t1.name
from login_info t1,login_info t2,login_info t3
where t1.sl=t2.sl and t2.sl=t3.sl and t1.sl=t3.sl
and t1.name!=t2.name and t2.name!=t3.name
and t3.name!=t1.name
and t3.rowid>t1.rowid
and t1.rowid>t2.rowid
/
Re: Required urgent solution [message #194939 is a reply to message #194925] Tue, 26 September 2006 04:52 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, your solution relies on the rowid recording the order that rows are inserted in, and this just doesn't happen.
You need an extra column to order your data by.
Previous Topic: Importing only packages
Next Topic: Performance Slow
Goto Forum:
  


Current Time: Thu Dec 08 06:36:06 CST 2016

Total time taken to generate the page: 0.13087 seconds