title: SQL Pivot Command
body: <p>I am looking for some help on designing a simple pivot so that I can link it into other parts of my queries.</p>
  
  <p>My data is like this</p>
  
  <p>Items Table</p>
  
  <p>Below is my table if I run <code>Select * from items</code></p>
  
  <pre><code>ITEM      Weight    
  12345         10    
  12345         11    
  654321        50    
  654321        20    
  654321       100
  </code></pre>
  
  <p>There are hundreds of Items in this table but each item code will only ever have 
  maximum of 3 weight records each.</p>
  
  <p>I want the desired output</p>
  
  <pre><code>ITEM     Weight_1    Weight_2     Weight_3
  12345          10          11         null
  654321         50          20          100
  </code></pre>
  
  <p>Would appreciate any suggestions,
  I have played around with pivots but each subsequent item puts the weights into weight 4,5,6,7,etc
  instead of starting at weight1 for each item.</p>
  
  <p>Thanks</p>
  
  <p><strong>Update</strong></p>
  
  <p>Below is what I have used so far, </p>
  
  <pre><code>SELECT r.*
  FROM   (SELECT 'weight' + CAST(Row_number() OVER (ORDER BY regtime ASC)AS
                                 VARCHAR(10))
                        line,
                 id,
                 weight
          FROM   items it) AS o PIVOT(MIN([weight]) FOR line IN (weight1, weight2,
         weight3)) AS r  
  </code></pre>