title: How to transform vertical data into horizontal data with SQL?
body: <p>I have a table "Item" with a number of related items, like so:</p>
  
  <pre><code>ID   Rel_ID  Name  RelRank
  ---  ------  ----  -------
  1    1       foo   1
  2    1       bar   2
  3    1       zam   3
  4    2       foo2  1
  </code></pre>
  
  <p>I'm trying to get a query so items with the same Rel_ID would appear in the same row, like so:</p>
  
  <pre><code>Rel_ID  Name1  Name2  Name3
  ------  -----  -----  -----
  1       foo    bar    zam
  2       foo2
  </code></pre>
  
  <p>I've tried selecting the table multiple times:</p>
  
  <pre><code>SELECT k.Rel_ID, k.name 'Name1', k2.name 'Name2'
  FROM item k, item k2
  WHERE k.Rel_ID = k2.Rel_ID
  </code></pre>
  
  <p>But this fails. Surely there's a transformation or query that could drastically simplify the process, and I'm just missing it because I haven't used SQL in this way before. What am I missing?</p>
  
  <p>[Edit: added RelRank column, which does appear in my data]</p>