This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Oct 10, 2012 8:15 AM by BluShadow Go to original post RSS
  • 15. Re: Please help with report format
    Chandran Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points