Home » SQL & PL/SQL » SQL & PL/SQL » Matrix query from a single table
icon5.gif  Matrix query from a single table [message #259844] Thu, 16 August 2007 12:00 Go to next message
Rashmi_Katariya
Messages: 9
Registered: April 2007
Junior Member
Hi,
I have a table Product_Model with following data:

Id Name Processor OS

1 M1 Pentium Windows
1 M1 Pentium Unix
1 M1 Pentium Linux
2 M1 Xeon Windows
2 M1 Xeon Netware
3 M2 Pentium Other

I want my result to be as follows:

ID Name Processor Windows Unix Linux Netware Others
1 M1 Pentium Yes Yes Yes No No
2 M1 Xeon Yes No No Yes No
3 M2 Pentium No No No No No


I have tried using the following query:

select id,name,processor,
decode(o.name,'Windows','Yes')Windows,
decode(o.name,'Unix','Yes')Unix,
decode(o.name,'Linux','Yes')Linux,
decode(o.name,'NetWare','Yes')NetWare,
decode(o.name,'Other','Other')Other
from Product_model;

With this query i get 6 rows with blank OS values against the model Id's as follows:

ID Name Processor Windows Unix Linux Netware Others
1 M1 Pentium Yes
1 M1 Pentium Yes
1 M1 Pentium Yes
2 M1 Xeon Yes
2 M1 Xeon Yes
3 M2 Pentium Yes


However, i want the same model id's to be in a single row.
Can anyone help me with this? Any help appreciated.

Thanks,
Rashmi.



1 M1 Pentium Yes Yes Yes No No
2 M1 Xeon Yes No No Yes No
3 M2 Pentium No No No No No
Re: Matrix query from a single table [message #259850 is a reply to message #259844] Thu, 16 August 2007 12:21 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Please read the sticky on how to format your posts as it is unreadable. Is it readable to you? How do you know what data lines up with what columns?
Re: Matrix query from a single table [message #259851 is a reply to message #259844] Thu, 16 August 2007 12:27 Go to previous messageGo to next message
jrich
Messages: 35
Registered: February 2006
Member
You need a group by clause in your sql. Something like this should work:

select id,name,processor,
max(decode(o.name,'Windows','Yes')) Windows,
max(decode(o.name,'Unix','Yes')) Unix,
max(decode(o.name,'Linux','Yes')) Linux,
max(decode(o.name,'NetWare','Yes')) NetWare,
max(decode(o.name,'Other','Other')) Other
from Product_model
group by id, name, processor;


JR
icon8.gif  Re: Matrix query from a single table [message #260028 is a reply to message #259844] Fri, 17 August 2007 03:28 Go to previous messageGo to next message
Rashmi_Katariya
Messages: 9
Registered: April 2007
Junior Member
Hey Joy,Iam really sorry for the unformatted post. I dont know how i just forgot to format the post..

Thanks for the reply JRICH, Shall check the solution and get back.

Thanks,
Rashmi.
Re: Matrix query from a single table [message #260102 is a reply to message #260028] Fri, 17 August 2007 07:46 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
That's ok, at least you acknowledged it instead of just ignoring it.
Previous Topic: Fast database generation
Next Topic: SELECT...FOR UPDATE
Goto Forum:
  


Current Time: Thu Dec 08 14:29:40 CST 2016

Total time taken to generate the page: 0.15500 seconds