Overview

SELECT 
	VendorID, 
	[250] AS Emp1, 
	[251] AS Emp2, 
	[256] AS Emp3,
	[257] AS Emp4, 
	[260] AS Emp5
FROM 
(
	SELECT 
		PurchaseOrderID, 
		EmployeeID, 
		VendorID
	FROM Purchasing.PurchaseOrderHeader
) AS p
PIVOT
(
	COUNT (p.PurchaseOrderID)
	FOR p.EmployeeID IN ([250], [251], [256], [257], [260])
) AS [pvt]
ORDER BY VendorID;

• To unpivot the table, assume the result set produced in the previous example is stored as pvt.

CREATE TABLE pvt 
(
	VendorID int, 
	Emp1 int, 
	Emp2 int,
	Emp3 int,
	Emp4 int,
	Emp5 int
);
GO

INSERT INTO pvt
VALUES
(1,4,3,5,4,4),
(2,4,1,5,5,5),
(3,4,3,5,4,4),
(4,4,2,5,5,4),
(5,5,1,5,5,5);

SELECT VendorID, 
	Employee, 
	Orders
FROM
(
	SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 
	FROM pvt
) AS [Pivot]
UNPIVOT 
(
	Orders FOR Employee 
	IN (Emp1, Emp2, Emp3, Emp4, Emp5)
) AS [UNPIVOT]