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

    Indesidered "crosstab" Output

    Hi to All,

    on this VIEW of name anni_rep_schema_ord :

    Code:
           descr_reparto         | anno |      totale      
    ------------------------------+------+------------------
     103  Frutta e Verdura        | 2009 | 5586.4002866377
     103  Frutta e Verdura        | 2010 | 2109.9550668092
     103  Frutta e Verdura        | 2011 | 7086.1099033701
     103  Frutta e Verdura        | 2012 | 13245.227153983
     112 Surgelati & gelati       | 2009 |     6813.832288
     101  Carne                   | 2009 | 4813.3710760001
     101  Carne                   | 2010 |     5488.61286
     101  Carne                   | 2011 |8559.449184672
     101  Carne                   | 2012 | 6836.628978154
       Pesce Secco                | 2009 | 5632.94520000001
       Pesce Secco                | 2010 |        1384.5564
       Pesce Secco                | 2011 | 907.209999999999
       Pesce Secco                | 2012 |           128.83
     104  Formaggi                | 2009 |       2154.1772
     104  Formaggi                | 2010 |     1528.560132
     104  Formaggi                | 2011 |     1310.586864
     104  Formaggi                | 2012 | 3152.1903100003
     105  Salumi                  | 2009 | 8240.46968000001
     105  Salumi                  | 2010 |        16940.759
     105  Salumi                  | 2011 | 3884.0097000001
     105  Salumi                  | 2012 | 1544.7235476761
     Varie                        | 2009 | 8624.1247600004
     Varie                        | 2010 |  48654.60850425
     Varie                        | 2011 | 4134.6070000004
     Varie                        | 2012 | 485.42999999993
     Bar                          | 2009 |            11.69
     Bar                          | 2010 |                0
     Bar                          | 2011 | 4529.8500000005
     Bar                          | 2012 | 9053.3500000003
     Olio di Oliva                | 2010 | 2112.110000
    Whith this command:

    Code:
    SELECT * FROM crosstab(
       'select descr_reparto, anno, totale from anni_rep_schema_ord order by 1',
       'select m from generate_series(1,4) m'
    )  as (
       descr_reparto text,
     "Anno_2009" double precision,
     "Anno_2010" double precision,
     "Anno_2011" double precision,
     "Anno_2012" double precision
    );

    I obtained this indesidered result
    Code:
           descr_reparto          | Anno_2009 | Anno_2010 | Anno_2011 | Anno_2012 
    ------------------------------+---------  ---+---------------+-----------+-----------
     101  Carne                    |           |          |              | 
     102  Pesce Fresco            |           |           |           |          
     103  Frutta e Verdura        |           |           |           |          
     104  Formaggi                |           |           |           |          
     105  Salumi                  |           |           |           |          
     106  Latte,Burro, Uova       |           |           |           |          
     107  Yogurt & Dessert        |           |           |           |          
     108  Pane & pasticceria      |           |           |           |          
     110  Prodotti Alimentari     |           |           |           |          
     112 Surgelati & gelati       |           |           |           |          
     113 Succhi&Bibite            |           |           |           |          
     114 Acqua e Birra            |           |           |           |          
     115 Vini & spumanti          |           |           |           |          
     116 Aperitivi & liquori      |           |           |           |          
     117 Amici Animali            |           |           |           |          
     119 Cura e bellezza          |           |           |           |          
     121 Detersivi & pulizia Casa |           |           |           |          
     122 Carta & plastica         |           |           |           |          
     123 Alluminio e pellicole    |           |           |           |          
     125 Tessile & casalinghi     |           |           |           |          
     127 Lampadine e ile          |           |           |           |          
     Bar                          |           |           |           |          
     Carne Bovina e di vitello    |           |           |           |          
     Carne Sarda                  |           |           |           |          
     Carne suina                  |           |           |           |          
     Dolciumi                     |           |           |           |          
     Ingrosso Casalinghi          |           |           |           |          
     Olio di Oliva                |           |           |           |          
       Pesce Secco                |           |           |           |          
     Varie                        |           |           |           |          
    (30 rows)

    instead of this desidered and sceemated:

    Code:
          descr_reparto         | Anno_2009 | Anno_2010 | Anno_2011 | Anno_2012 
    ------------------------------+-----------+-----------+-----------+-----------
    101  Carne                  |  NUMBER    |NUMBER      |NUMBER |  NUMBER      
    102  Pesce Fresco           |etc.           |           |           |

    FOR NUMBER I INTEND THE VALUE OF THE COLON "totale".

    Could Anyone find the solution and correct the mistake?

    Tanks in advance.

  2. #2
    Join Date
    Dec 2012
    Posts
    30
    Hi,
    I corect two errors of orthography:


    instead of this desidered and sceemated: =
    instead of this desidered and schemated:

    FOR NUMBER I INTEND THE VALUE OF THE COLON "totale". =
    FOR NUMBER I INTEND THE VALUE OF THE COLUMN "totale". (of the View
    anni_rep_schema_ord )

  3. #3
    Join Date
    Dec 2012
    Posts
    30
    RESOLVED

    Hi,

    The solution is to put "generate_series(2009,2012)" and not "generate_series(1,4)" :

    Code:
    SELECT * FROM crosstab(
       'select descr_reparto, anno, totale from anni_rep_schema_ord order by 1',
       'select m from generate_series(2009,212) m'
    )  as (
       descr_reparto text,
     "Anno_2009" double precision,
     "Anno_2010" double precision,
     "Anno_2011" double precision,
     "Anno_2012" double precision
    );
    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
  •