title: SQL Server 2008 Pivot without Aggregation
body: <p>I am facing a problem trying to perform a pivot on the table. A sample of what I want is as shown below.</p>
  
  <pre><code>ProductBarcode    ProductID
  --------------    ---------
  1000              P1
  1001              P1
  1002              P2
  1003              P3
  1004              P4
  1005              P4
  </code></pre>
  
  <p>Now I want to transform the above table into something as below.</p>
  
  <pre><code>ProductID    Barcode1    Barcode2
  ---------    --------    --------
  P1           1000        1001
  P2           1002        
  P3           1003        
  P4           1004        1005
  </code></pre>
  
  <p>I was trying to work it out with the following query but it wasn't giving the required results:</p>
  
  <pre><code>SELECT 
    [r1].[productID],
    [r1].[Productbarcode] as Barcode1,
    [r2].[ProductBarcode] as Barcode2
  FROM products as r1 right JOIN products as r2 on r1.[productID] = r2.[productID]
  </code></pre>
  
  <p>Now this is just an example and in actual case, there are hundreds of products which have multiple barcodes.</p>
  
  <p>I have even tried using the following query but all I got was a null in both the barcode columns.</p>
  
  <pre><code>SELECT productID,[barcode1],[barcode2]
  FROM
  (SELECT barcode, productID
  FROM products) as TableToBePivoted
  PIVOT
  (MAX(barcode)
  FOR barcode IN ([barcode1], [barcode2])
  ) AS PivotedTable;
  </code></pre>
  
  <p>Any help would be greatly appreciated.</p>