Oralce query question

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.
toookiAsked:
Who is Participating?
 
tigin44Connect With a Mentor Commented:
this may help you

SELECT ID, MIN(F1) AS F1, MIN(F2) AS F2, MIN(F3) AS F3
FROM yourTable
GROUP BY ID
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
 
toookiAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.