title: PostgreSQL cross tab with three columns with values summed from one column
body: <p>I am new to SQL and was trying to do a crosstab in Postgres. I would have done it in Excel, but I have a database of around 3.5 million rows, 20,000 different values for code, 7 categories in cat, and variable values from 1 to 100. A code may only have few of the 7 categories. </p>
  
  <p>Excel can't handle the number of rows, so SQL it is.</p>
  
  <p>My data is in the form </p>
  
  <pre><code>code   |  cat        |   value |
  --------------------------------
  abc123 |   1         |    4    |
  abc234 |   2         |    6    |
  abc345 |   1         |    1    |
  abc123 |   3         |    2    |
  abc123 |   6         |    12   |
  </code></pre>
  
  <p>with code and cat as text, value as integer stored in a Postgres table. </p>
  
  <p>I would like to perform a crosstab on code and cat, with sum of value. I would like it to show zero instead of 'null' in the return, but if 'null' would be simpler query, then that would be fine. </p>
  
  <p>So the output I would like is </p>
  
  <pre><code>code   |   'cat=0' | 'cat=1' | 'cat=2' | 'cat=3' | 'cat=4' | 'cat=5' | 'cat=6'|
  abc123 |    25     |  0      |  3      |  500    | 250     | 42      |  0     |
  abc234 |     0     |  100    |  0      |   10    |  5      |  0      |   25   |
  abc345 |    1000   |   0     |  0      |    0    |  0      |  0      |   0    |
  </code></pre>
  
  <p>I have searched on Postgres help files and other forums; the closest thing was the SO question <a href="https://stackoverflow.com/questions/3002499/postgresql-crosstab-query">PostgreSQL Crosstab Query</a> but I couldn't figure out how to sum the values from third column. </p>
  
  <p>Any assistance would be greatly appreciated.</p>