1 2 Previous Next 21 Replies Latest reply: Oct 10, 2012 10:15 AM by BluShadow Go to original post RSS
      • 15. Re: Please help with report format
        Chandran
        Thanks to Frank and Janeesh,

        Got the solution and working fine, anyways once again i will go through the Frank links.

        Thanks
        Chandran
        • 16. Re: Please help with report format
          Chandran
          Hi,

          How i can use dynamic values in below query

          My requirement is like I have a table with multiple month values ex: 2012-01,2012-02...etc

          1 ) how i can give current month name and previous month name Instead of month1_amt and month2_amt column names ?
           SQL> SELECT status,
            2         sum(month1_amt) month1_amt,
            3         sum(month2_amt) month2_amt
            4  FROM GENERAL
            5  UNPIVOT(AMT FOR status IN ( registered AS 'REGISTERED',accepted AS 'ACCEPTED',rejected AS 'REJECTED'))
            6  PIVOT(SUM(AMT) as amt FOR MONTH IN ('2012-04' AS MONTH1,'2012-05' AS MONTH2))
            7  group by status;
          2 ) In the above query we are hard coding the '2012-04' and '2012-05', but this should be dynamic.

          latest two month values we should give, how i can do this ?

          finally, report should be like
           SQL> SELECT status,
            2         sum(month1_amt) previous_month_name,
            3         sum(month2_amt) current_month_name
            4  FROM GENERAL
            5  UNPIVOT(AMT FOR status IN ( registered AS 'REGISTERED',accepted AS 'ACCEPTED',rejected AS 'REJECTED'))
            6  PIVOT(SUM(AMT) as amt FOR MONTH IN ('prevous_month_value' AS MONTH1,'current_month_value' AS MONTH2))
            7  group by status;
          
          
           STATUS        April                          May
          ----------          ----------                     ----------
          REGISTERED     210                         140
          REJECTED         90                            60
          ACCEPTED        120                           80
          I should have mention this before only sorry for this and please suggest any solution for this.

          Thanks
          Chandran

          Edited by: Chandran on Oct 6, 2012 2:19 AM
          • 17. Re: Please help with report format
            Frank Kulash
            Hi,
            Chandran wrote:
            How i can use dynamic values in below query
            See "Dynamic Pivoting" in the forum FAQ {message:id=9360005}
            • 18. Re: Please help with report format
              Chandran
              Hi ,

              Still i am getting the different requirements based on pivot and unpivot.

              Really thank for your help 'Frank' and 'Janeesh', your links are very useful to me.

              Now report should be like
                                             2012                          2011                
                                  2012-09        2012-08    2011-09    2011-08
              enrolled            50                 80         70             40
              accepted          40                 70         50             40
              rejected           10                10         20               0
              Table data like :
                month        year   enrolled  accepted rejected
              2012-09       2012       50        40          10
              2012-08       2012       80        70          10
              2011-09       2012       70         50         20
              2011-08       2011       40         40           0
              Please give me any useful links or sample code on this..

              Thanks
              Chandran

              Edited by: Chandran on Oct 10, 2012 6:30 AM
              • 19. Re: Please help with report format
                BluShadow
                I think you've been given all the userful links.

                Part of the problem seems to be that you want dynamic column headings in your results.
                Without doing dynamic queries that's not possible because SQL projection (the column names and datatypes) returned by a query must be known before any data is fetched.

                Read: {thread:id=2309172}

                So the only way to dynamically generate different headings is either using the method described in the link you've been provided previously, or by writing dynamic code yourself, which would have to query the data once to determine what the column headings should be, and then build a dynamic query based on that to query and pivot the data with that dynamic projection.

                Typically, such a requirement is not suited to being done in SQL, and is something better done by reporting tools, as they will query the data for a report, and then have the ability to pivot and format data as you require (most reporting tools can do that), based on the data that has been returned.
                • 20. Re: Please help with report format
                  Chandran
                  Thanks for your reply Shadow,

                  sorry for my incomplete information, now what i need is multi pivot concept

                  data should be like :
                                                
                                                                                                 2012                          2011                
                  ----------------------------------------------------------------------------------
                                      2012-09        2012-08    2011-09    2011-08
                  ------------------------------------------------------------------------------------
                  enrolled            50                 80         70             40
                  accepted          40                 70         50             40
                  rejected           10                10         20               0
                  Thanks
                  Chandran

                  Edited by: Chandran on Oct 10, 2012 6:58 AM
                  • 21. Re: Please help with report format
                    BluShadow
                    Yep, that's definitely something for a report writing tool to handle. Yes, you could do it in PL/SQL, but why re-invent the wheel when report writing tools have been designed for this purpose.
                    1 2 Previous Next