Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2012
    Posts
    30

    Unanswered: Is it Possible with a Complex Query?

    Hi to All,

    Excuse for the perhaps hard question.

    I have a table "invoice_lines" of the form:
    Code:
    Year   |week	|item	|matter 	|total|
           +        +       +
    2012   |1  	|book_1 |Philosopy      |37,50|
    2012   |1  	|book_1 |Philosopy      |37,50|
    2012   |1  	|book_2 |Story          |47,50|
    2012   |1  	|book_2 |Story          |47,50|
    2012   |2  	|book_1 |Philosopy      |37,50|
    2012   |2  	|book_2 |Story          |47,50|
    2012   |2  	|book_2 |Story          |47,50|
    2012   |3  	|book_1 |Philosopy      |37,50|
    2012   |3  	|book_1 |Philosopy      |37,50|
    2012   |3  	|book_1 |Philosopy      |37,50|
    2012   |3  	|book_2 |Story          |47,50|
    2012   |3  	|book_2 |Story          |47,50|
    2012   |3  	|book_2 |Story          |47,50|
    2012   |4  	|book_2 |Story          |47,50|
    Strating from this table, I'd want arrive to obtain a view as:

    Code:
    Year |    matter   |total_week1 |total_week2  |total_week3  |total_week4|... total_week53|
         +             +            +             +             +
    2012 |    Philosopy|      75,00 |       37,50 |       112,50|          0|               0|
    2012 |        Story|      95,00 |       95,00 |       142,50|      47,50|               0|
    Could SomeOne tell if is it possible?

    If yes, trough which query?

    Tanks in advance

    Gianni Pinna.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    This is called "pivot" and is not something that relation databases are good at. There is the "tablefunc" module Postgres that gives you a "crosstab" function for precisely this requirement.

    PostgreSQL: Documentation: 9.2: tablefunc
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Dec 2012
    Posts
    30
    Hi Shammat,


    tank You for Your promt replay, with wich You immediately clarify the limits.


    I'll read Your suggested link.

    Best Rgrds.

    Gianni Pinna.

Posting Permissions

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