Home » SQL & PL/SQL » SQL & PL/SQL » Interesting Query
icon14.gif  Interesting Query [message #234140] Mon, 30 April 2007 02:39 Go to next message
Messages: 23
Registered: April 2007
Location: Noida
Junior Member
select * from case_Data where casenum in (1105515,1105709);

This was the output when the above query is executed

casenum field_name      field_value
1105515 Date recived    26/07/07
1105515 doctype         NDF
1105515 FAXNUMBER       Fax to 113131
1105709 Datarecived     28/07/07
1105709 doctype         INCOMING
1105709 FAXNUMBER       Fax to 112121

But I want the output as Below

casenum field_name field_value field_name field_value field_name field_value
1105515 Date received 26/07/07 doctype NDF FAXNUMBER Fax to 113131
1105709 Date received 28/07/07 doctype INCOMING FAXNUMBER Fax to 112121 

[Mod-edit]applied code tags. Next time do so yourself. Read the first two topics (sticky) in this forum.

[Updated on: Mon, 30 April 2007 04:25] by Moderator

Report message to a moderator

Re: Interesting Query [message #234185 is a reply to message #234140] Mon, 30 April 2007 05:45 Go to previous message
Michel Cadot
Messages: 63911
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select casenum,
       max(decode(rn,1,field_name)) field_name,
       max(decode(rn,1,field_value)) field_value,
       max(decode(rn,2,field_name)) field_name,
       max(decode(rn,2,field_value)) field_value,
       max(decode(rn,3,field_name)) field_name,
       max(decode(rn,3,field_value)) field_value
from (select a.*,
             decode(field_name,'Date received',1,'doctype',2,'FAXNUMBER',3) rn
      from case_data a
group by casenum


Previous Topic: Multiple values in CASE statement
Next Topic: How To Set Thursday and Friday as Weekends in the database
Goto Forum:

Current Time: Sat Oct 22 08:32:57 CDT 2016

Total time taken to generate the page: 0.08424 seconds