Home » SQL & PL/SQL » SQL & PL/SQL » Matrix like SQL
Matrix like SQL [message #332876] Thu, 10 July 2008 00:31 Go to next message
arksjd
Messages: 13
Registered: November 2007
Junior Member
Hi

In the table T1 , the values are like this
RowVal  COlVal Actuals
------  ------ ------- 
1        Mean    3
2        Mean    4
1        Mode    5
2        Mode    6    

I want a report in the format given below.

   Mean   Mode
1   3      5 
2   4      6 


How do I write an SQl to get the above format.Kindly help me.
Thank
Rajesh.

Re: Matrix like SQL [message #332879 is a reply to message #332876] Thu, 10 July 2008 00:34 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
How many times this question will be asked.

Search Matrix Query in this forum.You will get a hell lot of results.

Regards,
Rajat
Re: Matrix like SQL [message #332881 is a reply to message #332876] Thu, 10 July 2008 00:39 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Not only in this forum, even wiki contains some useful entries:
http://www.orafaq.com/wiki/PIVOT
http://www.orafaq.com/wiki/SQL_FAQ#How_does_one_code_a_matrix.2Fcrosstab.2Fpivot_report_in_SQL.3F
Re: Matrix like SQL [message #332893 is a reply to message #332876] Thu, 10 July 2008 01:02 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
First do the search then you will get many solutions.

SELECT rowval, MAX(DECODE(colval,'Mean',actuals)) Mean,
	   		  MAX(DECODE(colval,'Mode',actuals)) Mode1
FROM t1
GROUP BY rowval



Thanks,
Re: Matrix like SQL [message #332894 is a reply to message #332893] Thu, 10 July 2008 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel
Re: Matrix like SQL [message #332895 is a reply to message #332893] Thu, 10 July 2008 01:05 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
ora_2007 this query will work when you know ColVal is Mean And Mode.

You have to manually check values in this.How will you check if thier are 200 distinct values in table for colval.

This option is good when you have less number of distinct values.

Regards,
Rajat

[Updated on: Thu, 10 July 2008 01:06]

Report message to a moderator

Re: Matrix like SQL [message #332897 is a reply to message #332895] Thu, 10 July 2008 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you really want a report with 200+ columns?
Who can display this?
Who can print this?
Who can read this?

Regards
Michel

[Edit: add missing word]

[Updated on: Thu, 10 July 2008 01:21]

Report message to a moderator

Re: Matrix like SQL [message #332902 is a reply to message #332897] Thu, 10 July 2008 01:13 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Dear Michel,

Yes you are right.
No one will be able to do what you have asked.

But i am trying to say is in this approach we have to manually define all entries.If thier are only say 20 columns(Now that's possible in a report).

When you write query in this format you have to change your query every time when a new value is added in table.Say now customer wants 21 columns in report for a different value.

The better approach is to automate this query.Don't you think that will be scalable and better approach and for that we have to use matrix query.

Regards,
Rajat
Re: Matrix like SQL [message #332908 is a reply to message #332876] Thu, 10 July 2008 01:21 Go to previous messageGo to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
Self Join can also achieve this task

select a.RowVal  , a.Actuals "mean", b.Actuals "mode" 
from T1 a, T1 b
where a.RowVal  = b.RowVal  
and a.COlVal = 'mean' and b.COlVal = 'mode'


Re: Matrix like SQL [message #332910 is a reply to message #332902] Thu, 10 July 2008 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have 3 ways (assuming a "paper" report):
- static query -> scalable: high, maintainable: low
- automatic SQL query -> scalable: low, maintainable: high
- programmatic (first search the column, then build the query to search the values) -> scalable: medium, maintainable: medium

Regards
Michel
Re: Matrix like SQL [message #332912 is a reply to message #332908] Thu, 10 July 2008 01:28 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
adit_me1 wrote on Thu, 10 July 2008 08:21
Self Join can also achieve this task

select a.RowVal  , a.Actuals "mean", b.Actuals "mode" 
from T1 a, T1 b
where a.RowVal  = b.RowVal  
and a.COlVal = 'mean' and b.COlVal = 'mode'


And if you have 20 values, you have 20 (full outer) joins... you follow me?

Regards
Michel
Previous Topic: DATE Logic
Next Topic: any method or package in UTL_SMTP to verfiy email address
Goto Forum:
  


Current Time: Wed Dec 07 20:49:29 CST 2016

Total time taken to generate the page: 0.13853 seconds