Home » SQL & PL/SQL » SQL & PL/SQL » Sql problem (oracle 9i)
Sql problem [message #340156] Mon, 11 August 2008 13:02 Go to next message
pkjoshi07
Messages: 1
Registered: August 2008
Junior Member
I have a problem I have a table like
ROL_NO          NAME           ADDRESS
10              Pramod          a b c
10              singh           dayalpur
10              Negi            delhi

now I want ouput like this
ROL_No F_NAME   M_NAME   L_NAME   ADD1     ADD2         ADD3
10     Pramod     singh    negi    a b c   dayalpur     Delhi

please help me to generate the query.

Thanks & regards,
Pramod

[Edit MC: Add code tags]

[Updated on: Mon, 11 August 2008 13:10] by Moderator

Report message to a moderator

Re: Sql problem [message #340158 is a reply to message #340156] Mon, 11 August 2008 13:05 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Sql problem [message #340162 is a reply to message #340156] Mon, 11 August 2008 13:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Align the columns in result.
Use the "Preview Message" button to verify.

Also always post your Oracle version (4 decimals).

Post what you tried and search before posting, this is a "pivot" query asked each and every week (more, several times each week).

Regards
Michel
Re: Sql problem [message #340165 is a reply to message #340156] Mon, 11 August 2008 14:09 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
And how do you identify which value goes into which column. i.e. why does singh not come under f_name? or Delhi under Address_1?
Re: Sql problem [message #340215 is a reply to message #340165] Tue, 12 August 2008 00:33 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
what i think he is storing 3 rows for each ROL_NO.

1 Row contains First Name
2 Row Middle Name
3 Row Last Name

And the problem is that he need to put these rows data in one row.

Try these functions to solve this problem:-

1)ROW_NUMBER()(Analytical Functions)
2)Max(Decode(


SELECT
   MAX(DECODE(rn,1,rol_no)) rol_no,
   MAX(DECODE(rn,1,Name)) Fname,
   MAX(DECODE(rn,2,Name)) Mname,
   MAX(DECODE(rn,3,Name)) Lname,
   MAX(DECODE(rn,1,address)) Add1,
   MAX(DECODE(rn,2,address)) Add2,
   MAX(DECODE(rn,3,address)) Add3
 FROM
  (
   SELECT 
    row_number() over (PARTITION BY rol_no ORDER BY rol_no) rn,
    rol_no,
    name,
    address 
   FROM 
     <Table_Name>
  )


Regards,
Rajat

[Updated on: Tue, 12 August 2008 00:44]

Report message to a moderator

Re: Sql problem [message #340225 is a reply to message #340215] Tue, 12 August 2008 00:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rajatratewal wrote on Tue, 12 August 2008 07:33
what i think he is storing 3 rows for each ROL_NO.

1 Row contains First Name
2 Row Middle Name
3 Row Last Name


As pablolee said: which row contains which? There is no ordering column indicating WHICH is the first row of the set etc.
Re: Sql problem [message #340229 is a reply to message #340225] Tue, 12 August 2008 01:00 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Yes you are right Frank.
Thanks for pointing out.

Regards,
Rajat
Re: Sql problem [message #340234 is a reply to message #340229] Tue, 12 August 2008 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition:
Quote:
PARTITION BY rol_no ORDER BY rol_no

If it is partition by rol_no then all rows have the same the same rol_no and your order by clause is meaningless.
This clearly shows you than you don't/can't know which row is number 1, 2 or 3.

In the end, don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel
Re: Sql problem [message #340240 is a reply to message #340234] Tue, 12 August 2008 01:19 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

In addition:
Quote:
PARTITION BY rol_no ORDER BY rol_no

If it is partition by rol_no then all rows have the same the same rol_no and your order by clause is meaningless.


Sometimes it's better to provide some extra thing which is not required actually so that OP can try some extra things.Smile

Quote:
In the end, don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Keep that in mind next time.


Regards,
Rajat
Previous Topic: Function Row to Column
Next Topic: Help Needed in selecting the values from PL/SQL Table and Unable to Bulk Collect in PLSQL table (2 t
Goto Forum:
  


Current Time: Sun Dec 04 12:44:56 CST 2016

Total time taken to generate the page: 0.08982 seconds