Execute the following T-SQL script in Microsoft SQL Server Management Studio Query Editor to demonstrate the usage of the CTE-s and PIVOT for complex crosstab reporting:

-- SQL common table expression - CTE

-- T-SQL pivot operator - crosstab query - pivot rows into columns

-- MSSQL - pivot PO count and PO sum of TotalDue - double pivot - multiple pivots

USE AdventureWorks;

GO

WITH cteVendorPO

     AS (SELECT PurchaseOrderID,

                City = City,

                State = StateProvinceCode,

                Employee = FirstName + ' ' + LastName,

                Vendor = v.Name

         FROM   Purchasing.PurchaseOrderHeader poh

                INNER JOIN HumanResources.Employee e

                  ON poh.EmployeeID = e.EmployeeID

                INNER JOIN Person.Contact c

                  ON e.ContactID = c.ContactID

                INNER JOIN Purchasing.Vendor v

                  ON v.VendorID = poh.VendorID

                INNER JOIN Purchasing.VendorAddress va

                  ON v.VendorID = va.VendorID

                INNER JOIN Person.Address a

                  ON a.AddressID = va.AddressID

                INNER 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

                INNER JOIN HumanResources.Employee e

                  ON poh.EmployeeID = e.EmployeeID

                INNER JOIN Person.Contact c

                  ON e.ContactID = c.ContactID

                INNER JOIN Purchasing.Vendor v

                  ON v.VendorID = poh.VendorID

                INNER JOIN Purchasing.VendorAddress va

                  ON v.VendorID = va.VendorID

                INNER JOIN Person.Address a

                  ON a.AddressID = va.AddressID

                INNER JOIN Person.StateProvince sp

                  ON sp.StateProvinceID = a.StateProvinceID)

SELECT   pvt1.State,

         pvt1.City,

         pvt1.Vendor,

         [Erin Hagens POs] = pvt1.[Erin Hagens],

         -- T-SQL money formatting - format currency

         [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

         INNER 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

GO

/* Partial results

*/

------------