Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2008
    Posts
    2

    Unanswered: Rows And Columns As Columns

    Hi all. This is my normal query.


    select * from table
    order by item

    I get. For Example the following.

    Item Year Month1 Month2 Month3
    ---------------------------------------------
    abc 2005 1 22 4
    abc 2006 45 64 4
    abc 2007 99 46 99
    bbc 2005 54 64 4
    bbc 2006 2 4 77
    bbc 2007 46 7 98


    I want my records to be returned as follow as possible.

    Item | 2005 Month1 | 2005 Month2 | 2005 Month3 | 2006 Month1 | 2006 Month2 |
    abc | 1 | 22 | 4 | 45 | 64
    bbc | 54 | 64 | 4 | 2 | 4



    In My Live system this is what I got sovar. But it is not Dynamic. If I do it this way I have to add it each month / Year to come. Please Help.


    SELECT T$ITEM,
    SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$1 ELSE 0 END) AS "2005 JAN",
    SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$2 ELSE 0 END) AS "2005 FEB",
    SUM(CASE WHEN T$YEAR=2005 THEN T$AUPP$3 ELSE 0 END) AS "2005 MARCH",
    SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$1 ELSE 0 END) AS "2006 JAN",
    SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$2 ELSE 0 END) AS "2006 FEB",
    SUM(CASE WHEN T$YEAR=2006 THEN T$AUPP$3 ELSE 0 END) AS "2006 MARCH"
    FROM baandb.ttdinv750700
    GROUP BY T$ITEM

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    There is no way to do that in pure SQL, you need to use some PL/SQL or a report writing tool. I have documented a PL/SQL approach here on my blog.

Posting Permissions

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