Execute the following script in Query Editor to demonstrate the usage of the CTE-s and PIVOT for complex crosstab reporting:

USE AdventureWorks;
GO
WITH cteVendorPO
AS
(
SELECT PurchaseOrderID, City=City, State=StateProvinceCode,
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
JOIN Purchasing.VendorAddress va
ON v.VendorID=va.VendorID
JOIN Person.Address a
ON a.AddressID = va.AddressID
JOIN Person.StateProvince sp
ON sp.StateProvinceID=a.StateProvinceID

),
cteVendorTotalDue
AS
(
SELECT Employee=FirstName+' '+LastName, City=City, State=StateProvinceCode,
Vendor = v.Name, TotalDue
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
JOIN Purchasing.VendorAddress va
ON v.VendorID=va.VendorID
JOIN Person.Address a
ON a.AddressID = va.AddressID
JOIN Person.StateProvince sp
ON sp.StateProvinceID=a.StateProvinceID

)
SELECT pvt1.State,
pvt1.City,
pvt1.Vendor,
[Erin Hagens POs]=pvt1.[Erin Hagens], [Erin Hagens Amount]='$'+convert(varchar,pvt2.[Erin Hagens],1),
[Linda Meisner POs]=pvt1.[Linda Meisner], [Linda Meisner Amount]='$'+convert(varchar,pvt2.[Linda Meisner],1),
[Sheela Word POs]=pvt1.[Sheela Word], [Sheela Word Amount]='$'+convert(varchar,pvt2.[Sheela Word],1),
[Frank Pellow POs]=pvt1.[Frank Pellow], [Frank Pellow Amount]='$'+convert(varchar,pvt2.[Frank Pellow],1),
[Reinout Hillmann POs]=pvt1.[Reinout Hillmann], [Reinout Hillmann Amount]='$'+convert(varchar,pvt2.[Reinout Hillmann],1)
FROM cteVendorPO
PIVOT
(
COUNT (PurchaseOrderID)
FOR Employee IN
( [Erin Hagens], [Linda Meisner], [Sheela Word], [Frank Pellow], [Reinout Hillmann] )
) AS pvt1
join
cteVendorTotalDue
PIVOT
(
sum(totaldue)
FOR Employee IN
( [Erin Hagens], [Linda Meisner], [Sheela Word], [Frank Pellow], [Reinout Hillmann] )
) AS pvt2
ON pvt1.Vendor=pvt2.Vendor
AND pvt1.State=pvt2.State
and pvt1.City=pvt2.City
ORDER BY pvt1.State, pvt1.City, pvt1.Vendor