title: How to do Pivoting in PostgreSQL
body: <p>I am new to PostgreSQL. </p>
  
  <p>Suppose I have a table as under</p>
  
  <pre><code>colorname   Hexa    rgb rgbvalue
  Violet  #8B00FF r   139
  Violet  #8B00FF g   0
  Violet  #8B00FF b   255
  Indigo  #4B0082 r   75
  Indigo  #4B0082 g   0
  Indigo  #4B0082 b   130
  Blue    #0000FF r   0
  Blue    #0000FF g   0
  Blue    #0000FF b   255
  </code></pre>
  
  <p>If I do a Pivot in SQL Server as</p>
  
  <pre><code>SELECT colorname,hexa,[r], [g], [b]
  FROM
  (SELECT colorname,hexa,rgb,rgbvalue
      FROM tblPivot) AS TableToBePivoted
  PIVOT
  (
  sum(rgbvalue)
  FOR rgb IN ([r], [g], [b])
  ) AS PivotedTable;
  </code></pre>
  
  <p>I get the output as</p>
  
  <pre><code>colorname   hexa    r   g   b
  Blue    #0000FF 0   0   255
  Indigo  #4B0082 75  0   130
  Violet  #8B00FF 139 0   255
  </code></pre>
  
  <p>How to do the same using PostgreSQL?</p>
  
  <p>My attempt is</p>
  
  <pre><code>SELECT *
  FROM crosstab
  (
      'SELECT 
          colorname
          ,hexa
          ,rgb
          ,rgbvalue
      FROM tblPivot'
  )AS ct(colorname text, hexa text, rgb text, rgbvalue int);
  </code></pre>
  
  <p>But geting error:</p>
  
  <pre><code>ERROR:  function crosstab(unknown) does not exist
  LINE 2: FROM crosstab
               ^
  HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
  ********** Error **********
  ERROR: function crosstab(unknown) does not exist**
  </code></pre>
  
  <p>Is there any elegant way of doing so in PostgreSQL (any built in function...) What is the standard practice of doing so ?</p>