Results 1 to 3 of 3

Thread: matrix format..

  1. #1
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Unanswered: matrix format..

    hello sql developers..
    Iam trying to built a query to get the following output ..

    DEPTNO:10
    ------------------JAN'04-----FEB'04-----MAR'04------APR'04
    sum(Sal)--------s1-----------s2-----------s3-------------s4
    Count(ename)--c1-----------c2-----------c3-------------c4
    ...

    DEPTNO:20
    ------------------JAN'04-----FEB'04-----MAR'04------APR'04
    sum(Sal)--------s1-----------s2-----------s3-------------s4
    Count(ename)--c1-----------c2-----------c3-------------c4
    ...

    DEPTNO:30
    ------------------JAN'04-----FEB'04-----MAR'04------APR'04
    sum(Sal)--------s1-----------s2-----------s3-------------s4
    Count(ename)--c1-----------c2-----------c3-------------c4
    ...

    Got To get the values of s1..and c1...

    I require such kind output(hifens r not part of required output) for one of my report where I need to generate summations per month..

    Can someone help me in doing this..
    thanks in advance..

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: matrix format..

    This is really a job for a report-writing tool, but you can get close in SQL Plus:
    PHP Code:
    select deptno
    ,      sum(DECODE(month,'JAN'04', sal)) as "JAN'04"
    ,      sum(DECODE(month,'FEB'04', sal)) as "
    FEB'04"
    ,      sum(DECODE(month,'
    MAR'04'sal)) as "MAR'04"
    ,      sum(DECODE(month,'APR'04', sal)) as "APR'04"
    ,      sum(DECODE(month,'JAN'04', 1)) as "
    JAN'04"
    ,      sum(DECODE(month,'
    FEB'04'1)) as "FEB'04"
    ,      sum(DECODE(month,'MAR'04', 1)) as "MAR'04"
    ,      sum(DECODE(month,'APR'04', 1)) as "
    APR'04"
    from   t
    group by deptno
    order by deptno 
    You'll need to study the SQL Plus manual for details of the COLUMN command, BREAK command etc. to get prettier results.

  3. #3
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Re: matrix format..

    Originally posted by andrewst
    This is really a job for a report-writing tool, but you can get close in SQL Plus:
    PHP Code:
    select deptno
    ,      sum(DECODE(month,'JAN'04', sal)) as "JAN'04"
    ,      sum(DECODE(month,'FEB'04', sal)) as "
    FEB'04"
    ,      sum(DECODE(month,'
    MAR'04'sal)) as "MAR'04"
    ,      sum(DECODE(month,'APR'04', sal)) as "APR'04"
    ,      sum(DECODE(month,'JAN'04', 1)) as "
    JAN'04"
    ,      sum(DECODE(month,'
    FEB'04'1)) as "FEB'04"
    ,      sum(DECODE(month,'MAR'04', 1)) as "MAR'04"
    ,      sum(DECODE(month,'APR'04', 1)) as "
    APR'04"
    from   t
    group by deptno
    order by deptno 
    You'll need to study the SQL Plus manual for details of the COLUMN command, BREAK command etc. to get prettier results.
    thanks.. I could get the output using cfml..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •