Re: Anual Query

At 11:20 AM 11/28/05, Mauricio Fernandez A. wrote:
>Can any of you help me with this query?,  I need to retrieve the
>sum(units) and sum(cost) for each client in every month in the same
>row, something like this:
>
>client|UnJan|CostJan|UnFeb|CostFeb|UnMar|CostMar ...    |UnDec|CostDec
>- --------------------------------------------------------------------
>100058|580      |47384  |746  |87438  |536  |21653      ...     |857 
>|754875 (wrong
>data, just to show)
>
>Now I get this result using several querys (one for month and worse
>for client too) but this, of course its too expensive.
>
>Here is a table sample.
>
>  orderId  | client  | date        | product | Un | cost
>- ------------------------------------------------------
>  1001299  | 100058  | 20050103  | 759936  | 1  | 2375
>  1001299  | 100058  | 20050103  | 759928  | 1  | 2375
>  1001300  | 100078  | 20050103  | 759936  | 1  | 2375
>  1001301  | 100017  | 20050103  | 759928  | 1  | 2375
>  1001302  | 100009  | 20050103  | 759936  | 2  | 4750
>  1001302  | 100009  | 20050203  | 756304  | 1  | 6669
>  1001303  | 100009  | 20050203  | 759936  | 2  | 4750
>  1001304  | 100005  | 20050203  | 759936  | 20  | 47500
>  1001306  | 100013  | 20050203  | 759936  | 2  | 4750
>  1001306  | 100013  | 20050203  | 759928  | 2  | 4750
>  1001307  | 100013  | 20050203  | 759936  | 4  | 9500
>  1001308  | 100013  | 20050203  | 759936  | 2  | 4750
>  1001309  | 100050  | 20050303  | 759936  | 2  | 4750
>  1001310  | 100050  | 20050303  | 759936  | 4  | 9500


Search google and/or archives of this and/or other pgsql lists for 
"crosstab" and/or "pivot table".  I think there might also be something in 
contrib/ that could help.