Home   |   Register   |   Today's Posts   |   Rules   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > PostgreSQL > Indesidered "crosstab" Output

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 02-14-13, 14:34
Gianni Pinna Gianni Pinna is offline
Registered User
  
Join Date: Dec 2012
Posts: 29
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.
Reply With Quote
  #2 (permalink)  
Old 02-15-13, 16:20
Gianni Pinna Gianni Pinna is offline
Registered User
  
Join Date: Dec 2012
Posts: 29
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 )
Reply With Quote
  #3 (permalink)  
Old 02-19-13, 04:34
Gianni Pinna Gianni Pinna is offline
Registered User
  
Join Date: Dec 2012
Posts: 29
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
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.