14 Replies Latest reply on Oct 4, 2017 3:54 PM by Frank Kulash

    A Matrix Query

    Eslam_Elbyaly

      Hi,

      Suppose I have this table

      Create table emp(empno number, emp_data varchar2(90) , val varchar2(90));

      insert into emp values(1,'grade',1);

      insert into emp values(1,'job','clerk');

      insert into emp values(2,'grade',3);

      insert into emp values(2,'job','salesperson');

       

      I have a requirement for SQL query which should have run on 11g db and above.

      I want to retrieve the data this way

      empno
      grade
      job
      11clerk
      23salesperson
        • 1. Re: A Matrix Query
          Sven W.

          SQL can do that. But first consider if your reporting tool can do it too. A matrix report is just a special version of presenting data. Usually it is better to make such a conversion in the presentation layer and not in the data layer.

           

          For example in APEX it is as simple as creating an interactive report and doing a pivot there. Although there are a few restrictions on the datatypes ( might depend on APEX version).

          • 2. Re: A Matrix Query
            Frank Kulash

            Hi,

             

            Here's a way that works in any version, 8.1 or higher:

            SELECT    empno

            ,         MIN (CASE WHEN emp_data = 'grade' THEN val END)   AS grade

            ,         MIN (CASE WHEN emp_data = 'job'   THEN val END)   AS job

            FROM      emp

            GROUP BY  empno

            ORDER BY  empno

            ;

            Starting in Oracle 11.1, you can also use SELECT ... PIVOT, but I'm not sure that would be any simpler or more efficient for your needs.  Make sure you understand how this works before you look into SELECT ... PIVOT.

            • 3. Re: A Matrix Query
              Eslam_Elbyaly

              I know that, but we want it in the data layer.

              • 4. Re: A Matrix Query
                Sven W.

                The FAQ of this forum has a list that describes various ways how to do such a matrix SQL.

                 

                Re: 4. How do I convert rows to columns?

                • 5. Re: A Matrix Query
                  mathguy

                  I agree with the general philosophy of performing reporting tasks in the reporting layer.

                   

                  However, in this case the desired output is better than the input (!!!)  The EAV model, which is the OP's input, is suboptimal in many (most?) cases; the "desired output" looks much more like "standard" relational DB tables. Who knows, perhaps the OP and his team must put up with an EAV model on the back end, but they are writing a view so that much more sane queries can be written against the view. Even better, perhaps they are migrating: they will populate a new table with the output, and keep the input table only for archival purposes.

                  • 6. Re: A Matrix Query
                    Eslam_Elbyaly

                    Can anyone help with the query I need?

                    • 7. Re: A Matrix Query
                      mathguy

                      One thought here, especially if you are "migrating" from one data model to another:  in addition to what Frank posted, you may want to convert to proper data types. So, for example, if GRADE is really supposed to be NUMBER, you may want to wrap the expression that calculates it within TO_NUMBER(....)  Same for dates. This way, if your output is used in further computations, you will be able to do so properly (for example, date comparisons will not show 03-AUG-2017 before 03-JAN-2017, as a string comparison would). This is important even if you use the output as a view in further computations. If you are really creating a new TABLE from it (the "migration" concept, not sure if that is the correct technical word for it), then it is that much more important that the columns of the new table be in the proper data types; the inability to do so in an EAV model is among its most glaring weaknesses.

                      • 8. Re: A Matrix Query
                        mathguy

                        Eslam_Elbyaly wrote:

                         

                        Can anyone help with the query I need?

                         

                        ...what do you mean? What is wrong with Frank's answer?  (ANSWER: NOTHING!)

                        • 9. Re: A Matrix Query
                          Frank Kulash

                          Hi,

                          Eslam_Elbyaly wrote:

                           

                          Can anyone help with the query I need?

                          What's wrong with the query in reply #2?  Point our where it's getting the wrong results, and explain how you figure the right results in those places.

                          • 10. Re: A Matrix Query
                            Paulzip

                            Your design is an EAV (Entity, Attribute, Value) model, which are almost always a terrible design mistake leading to massive complications in the long run.  It generally manifests as the simplest task in a non EAV model becomes the most unnecessarily complicated task in an EAV one

                             

                            It might help you to read this thread as to why it's such a bad idea.  If you have chance now, please rethink your design.

                            • 11. Re: A Matrix Query
                              Eslam_Elbyaly

                              Sorry Frank, I did not notice your reply.

                              The problem is that there are more values besides 'grade', and 'job', and I want it to be dynamic, because there is a possibility in the future to add more and more values.

                              • 12. Re: A Matrix Query
                                Paulzip

                                Eslam_Elbyaly wrote:

                                 

                                Sorry Frank, I did not notice your reply.

                                The problem is that there are more values besides 'grade', and 'job', and I want it to be dynamic, because there is a possibility in the future to add more and more values.

                                Create a table with the columns...

                                empno, grade, job.

                                 

                                Then when you want to add "more values" just add new columns.

                                 

                                You'll thank me if you heed my advice, I 100% guarantee it.

                                • 13. Re: A Matrix Query
                                  L. Fernigrini

                                  Eslam_Elbyaly escribió:

                                   

                                  Sorry Frank, I did not notice your reply.

                                  The problem is that there are more values besides 'grade', and 'job', and I want it to be dynamic, because there is a possibility in the future to add more and more values.

                                  If the piece of code you are writing does not know in advance the number of columns, you need to generate it dynamically.

                                   

                                  There is no way to write a fixed sentence that will automatically add new columns, you need to dynamically construct it each time you need to run it, or at least after a new value is added or removed...

                                  • 14. Re: A Matrix Query
                                    Frank Kulash

                                    Hi,

                                    Eslam_Elbyaly wrote:

                                     

                                    Sorry Frank, I did not notice your reply.

                                    The problem is that there are more values besides 'grade', and 'job', and I want it to be dynamic, because there is a possibility in the future to add more and more values.

                                    You didn't say anything about making it dynamic in your original message.  Please mention all your requirements when you post a question; it will save time for everyone, including you.

                                     

                                    There are lots of ways to make a query like this dynamic.   (None of them are very good.)

                                    See

                                    https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4471013000346257238

                                    Re: Pivot Command, can the IN clause be dynamic rather than hard-coded values?

                                    Re: Report count and sum from many rows into many columns

                                    PIVOT SQL

                                    How to pipeline a function with a dynamic number of columns?

                                     

                                    Choose a technique that suits your requirements..

                                    If you get stuck, post your code and a specific question.