Home » SQL & PL/SQL » SQL & PL/SQL » Need to get value from one column but display id different fields. (SQL)
Need to get value from one column but display id different fields. [message #569195] Mon, 22 October 2012 10:12 Go to next message
punu77
Messages: 20
Registered: July 2005
Junior Member
Hello,

I need help to get members's home and work and cell phone number if it's avaiable. if not just disply null.

Table name : Test

member entity_id Phone_id phone_code Phone_num
abc 400175 200201 HME 49.6171.59501
abc 400175 200202 CEL 491.170.9174054
abc 400175 200203 WRK 49.6142.7.76675
def 521985 199991 HME 555-555-5555
ghi 345634 188881 HME 222-345-2345
ghi 345634 188882 CEL 222-456-6565


member Home_phn work_phn cell_phn
abc 49.6171.59501 491.170.9174054 49.6142.7.76675
xyz 555-555-5555
ghi 222-345-2345 222-456-6565


Thank you
Re: Need to get value from one column but display id different fields. [message #569196 is a reply to message #569195] Mon, 22 October 2012 10:16 Go to previous messageGo to next message
BlackSwan
Messages: 22481
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Need to get value from one column but display id different fields. [message #569197 is a reply to message #569196] Mon, 22 October 2012 10:21 Go to previous messageGo to next message
punu77
Messages: 20
Registered: July 2005
Junior Member
Did I posted the the question in wrong form?
Re: Need to get value from one column but display id different fields. [message #569198 is a reply to message #569197] Mon, 22 October 2012 10:26 Go to previous messageGo to next message
BlackSwan
Messages: 22481
Registered: January 2009
Senior Member
Does your posting comply with Posting Guidelines?
If not, consider responding with requested details.
Re: Need to get value from one column but display id different fields. [message #569205 is a reply to message #569198] Mon, 22 October 2012 13:07 Go to previous messageGo to next message
Flyby
Messages: 143
Registered: March 2011
Location: Belgium
Senior Member
select mymembers.member,telhome.phone_home,telwork.phone_work,telcel.phone_cell
from 
(
select member
from mytable
group by member
) mymembers
left join 
(
select member,Phone_num phone_home
from mytable
where phone_code ='HME'
) telhome
on mymembers.member=telhome.member
left join 
(
select member,Phone_num phone_work
from mytable
where phone_code ='WRK'
) telwork
on mymembers.member=telwork.member
left join 
(
select member,Phone_num phone_home
from mytable
where phone_code ='CEL'
) telcel
on mymembers.member=telcel.member

Re: Need to get value from one column but display id different fields. [message #569209 is a reply to message #569205] Mon, 22 October 2012 14:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1948
Registered: January 2010
Senior Member
Why not simply:

select  member,
        max(case phone_code when 'HME' then phone_num end) phone_home,
        max(case phone_code when 'WRK' then phone_num end) work_home,
        max(case phone_code when 'CEL' then phone_num end) cell_home
  from  mytable
  group by member
/


SY.
Re: Need to get value from one column but display id different fields. [message #569215 is a reply to message #569197] Tue, 23 October 2012 00:58 Go to previous message
Michel Cadot
Messages: 58489
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
punu77 wrote on Mon, 22 October 2012 17:21
Did I posted the the question in wrong form?


With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Previous Topic: multiple report parameter values
Next Topic: Query to find usernames from their Ids
Goto Forum:
  


Current Time: Wed Jul 23 00:54:49 CDT 2014

Total time taken to generate the page: 0.15023 seconds