Common Table Expression (CTE) is an alternative for using temporary tables, table variables or views. It is a temporary table result set whose scope is limited to the execution of a SELECT, INSERT, UPDATE, DELETE or CREATE VIEW statement. CTE is a virtual table which can be referenced several times in the associated query following it. Here is an example where CTE is applied in conjunction with a PIVOT:

USE AdventureWorks;
GO
WITH cteVendorPO
AS
(
SELECT PurchaseOrderID,
Employee=FirstName+' '+LastName,
Vendor = v.Name
FROM Purchasing.PurchaseOrderHeader poh
JOIN HumanResources.Employee e
ON poh.EmployeeID = e.EmployeeID
JOIN Person.Contact c
ON e.ContactID = c.ContactID
JOIN Purchasing.Vendor v
ON v.VendorID = poh.VendorID
)
SELECT *
FROM cteVendorPO v
PIVOT
(
COUNT (PurchaseOrderID)
FOR Employee IN
( [Erin Hagens], [Linda Meisner], [Sheela Word], [Frank Pellow], [Reinout Hillmann] )
) AS pvt
ORDER BY pvt.Vendor