title: How do I pivot a joined table in postgresql?
body: <p>I have a table 
  senators:
      sname (key) 
      party     &lt;--can be Democrat or Republican</p>
  
  <p>thats joined by table 
  sponsors:
      sname (fk)
      legnum</p>
  
  <p>I want to create a table thats formatted like this:</p>
  
  <pre><code> |Democrat |  Republican | legnum  |
   |Clinton  |   McCain    |  1234   |
  </code></pre>
  
  <p>****Basically I need to create a table using the list of senators and put two candidates (one from each party) that is sponsoring a bill (legnum). How do I do this in postresql? *****</p>
  
  <pre><code>SELECT Democrat, Republican, legnum
  FROM
       (select distinct sname, legnum, ROW_NUMBER() over
       (Partition By sname Order By sname) as ColumnSequence 
       from senators
       inner join sponsors
       on sponsors.sname = senators.sname)
  pivot
  ( 
       max(sname)
       for ColumnSequence in (Democrat, Republican))
   )PIV;
  </code></pre>
  
  <p>The above is a query I wrote that doesn't work. </p>