k.Lennartz k.Lennartz - 15 days ago 6
SQL Question

how to use pivot with self join

during some questions here i got this 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


i tried to use pivot like it is presented at [Micorsoft]but i get a syntax error near the for loop

outcome without Pivot is DEMO :

+------------------------------------+---------------------------------+----------------+
| 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 |
+------------------------------------+---------------------------------+----------------+


and with pivot it should do DEMO:

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

Answer

This doesnt really need pivot, just add one row_id to each row, and arrange it with GROUP BY

SQL DEMO

WITH add_id as (
    SELECT *,
           row_number() over (partition by [CompanyName] ORDER BY [ProductName]) as rn
    FROM Table1        
)       
SELECT [CompanyName],
       MAX(CASE WHEN rn = 1 THEN [ProductName] END) as [ProductName1],
       MAX(CASE WHEN rn = 2 THEN [ProductName] END) as [ProductName2],
       MAX([Category]) as [Category]
FROM add_id
GROUP BY [CompanyName]

OUTPUT

enter image description here

EDIT:

A simple version without row_number can be made if only two products and always two. If only one Product then Product1 and Product2 will be the same

SELECT [CompanyName],
       MIN([ProductName]) as [ProductName1],
       MAX([ProductName]) as [ProductName2],
       MAX([Category]) as [Category]
FROM Table1
GROUP BY [CompanyName]
Comments