Solved

Oralce query question

Posted on 2011-02-19
3
334 Views
Last Modified: 2012-05-11
I have an Oracle 11g table and its contents are like this:
ID   V1   F1   F2    F3
=======================
1    p    12   null  null    
2    q    null 11    null
2    r    null null  17
4    r    null null  23
5    p    8    null  null
4    p    18   null  null
4    q    null 56    null
7    p    17   null  null

How can I write a query that will give following O/P:
ID   F1   F2    F3
=======================
1    12   null  null    
2    null 11    17
4    18   56    23
5    8    null  null
7    17   null  null

The Value of V1 in the first table could only be "p", "q" or "r".
If V1=p then we only need its F1 value.
If V1=q then we only need its F2 value.
If V1=r then we only need its F3 value.
And the O/P needs to have unique ID value.
0
Comment
Question by:toooki
3 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 225 total points
Comment Utility
this may help you

SELECT ID, MIN(F1) AS F1, MIN(F2) AS F2, MIN(F3) AS F3
FROM yourTable
GROUP BY ID
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 225 total points
Comment Utility
Just in case the real world requirement requires a little more logic behind it, you can do something similar with case/decode statements:

select id,max(F1) F1,max(F2) F2,max(F3) F2
from
(
select id,
      case when V1='p' then F1 else null end F1,
      case when V1='q' then F2 else null end F2,
      case when V1='r' then F3 else null end F3
from tab1
)
group by id
/
0
 

Author Comment

by:toooki
Comment Utility
Yes the solution works perfectly.  But my mistake. I assumed that I could create a view like the below table on which I could use the query that you gave to me.
ID   V1   F1   F2    F3
=================

But I could not create such view. Could you please look at the question that I could correctly put here.
http://www.experts-exchange.com/Database/Oracle/Q_26833931.html
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now