title: how to use pivot with self join
body: <p>during some questions here i got this code</p>
  
  <pre><code>SELECT s.CompanyName ,[250] AS ProductName, [251] AS ProductName, pc.CategoryID
  FROM Suppliers s INNER JOIN (SELECT SupplierID, CategoryID
                           FROM Products
                           GROUP BY SupplierID,CategoryID
                           HAVING COUNT(1) = 2) pc ON (s.SupplierID = pc.SupplierID)
               INNER JOIN Products p ON(s.SupplierID = p.SupplierID AND pc.CategoryID = p.CategoryID)
  PIVOT
  (
    p.ProductName
    FOR SupplierID IN ([250], [251]) AS pvt
  )
  ORDER BY pvt.CompanyName
  </code></pre>
  
  <p>i tried to use pivot like it is presented at [Micorsoft]but i get a syntax error near the for loop</p>
  
  <p>outcome without Pivot is <a href="http://rextester.com/GEUICK61603" rel="nofollow noreferrer"><strong>DEMO</strong></a> : </p>
  
  <pre><code>+------------------------------------+---------------------------------+----------------+
  |                CompanyName         |              ProductName        |     CategoryID |   
  +------------------------------------+---------------------------------+----------------+
  | Exotic Liquids                     | Chai                            | Beverages      |
  | Exotic Liquids                     | Chang                           | Beverages      |
  | Aux joyeux ecclésiastiques         | Côte de Blaye                   | Beverages      |
  | Aux joyeux ecclésiastiques         | Chartreuse verte                | Beverages      |
  | Grandma Kelly's Homestead          | Grandma's Boysenberry Spread    | Condiments     |
  | Grandma Kelly's Homestead          | Northwoods Cranberry Sauce      | Condiments     |
  | Zaanse Snoepfabriek                | Zaanse koeken                   | Confections    |
  | Zaanse Snoepfabriek                | Chocolade                       | Confections    |
  | Karkki Oy                          | Maxilaku                        | Confections    |
  | Karkki Oy                          | Valkoinen suklaa                | Confections    |
  | Cooperativa de Quesos 'Las Cabras' | Queso Cabrales                  | Dairy Products |
  | Cooperativa de Quesos 'Las Cabras' | Queso Manchego La Pastora       | Dairy Products |
  | Gai pâturage                       | Raclette Courdavault            | Dairy Products |
  | Gai pâturage                       | Camembert Pierrot               | Dairy Products |
  | PB Knäckebröd AB                   | Gustaf's Knäckebröd             | Grains/Cereals |
  | PB Knäckebröd AB                   | Tunnbröd                        | Grains/Cereals |
  | Pasta Buttini s.r.l.               | Gnocchi di nonna Alice          | Grains/Cereals |
  | Pasta Buttini s.r.l.               | Ravioli Angelo                  | Grains/Cereals |
  | Ma Maison                          | Tourtière                       | Meat/Poultry   |
  | Ma Maison                          | Pâté chinois                    | Meat/Poultry   |
  | New England Seafood Cannery        | Boston Crab Meat                | Seafood        |
  | New England Seafood Cannery        | Jack's New England Clam Chowder | Seafood        |
  | Lyngbysild                         | Rogede sild                     | Seafood        |
  | Lyngbysild                         | Spegesild                       | Seafood        |
  +------------------------------------+---------------------------------+----------------+
  </code></pre>
  
  <p>and with pivot it should do <a href="http://rextester.com/HHAJ13265" rel="nofollow noreferrer">DEMO</a>:</p>
  
  <pre><code>    CompanyName 
      ProductName
      ProductName2
      Category
  
  1
      Exotic Liquids
      Chai
      Chang
      Beverages
  
  2
      Aux joyeux ecclésiastiques
      Côte de Blaye
      Chartreuse verte
      Beverages
  
  3
      Grandma Kelly's Homestead
      Grandma's Boysenberry Spread
      Northwoods Cranberry Sauce
      Condiments
  
  4
      Zaanse Snoepfabriek
      Zaanse koeken
      Chocolade
      Confections
  
  5
      Karkki Oy
      Maxilaku
      Valkoinen suklaa
      Confections
  </code></pre>