Execute the following TSQL example script in SQL Server Management Studio Query Editor to demonstrate how to apply CTE and PIVOT to create crosstab summary reports.

-- SQL Server Common Table Expression - CTE

-- SQL Server PIVOT operator for creating crosstab report

-- SQL convert rows to columns

-- SQL Server coalesce and stuff functions

USE AdventureWorks;

WITH cteQuarterBigOrders

     AS (SELECT DatePart(qq,OrderDate) AS Quarter,

                p.Name,

                OrderAmount = convert(MONEY,LineTotal)

         FROM   Sales.SalesOrderHeader AS soh

                INNER JOIN Sales.SalesOrderDetail AS sod

                  ON soh.SalesOrderID = sod.SalesOrderID

                INNER JOIN Production.Product AS p

                  ON sod.ProductID = p.ProductID

         WHERE  DatePart(yy,OrderDate) = 2003  -- alternate YEAR(OrderDate)

                AND sod.LineTotal >= 5000)

SELECT   ProductName = Name,

-- SQL Server currency formatting - format money with thousand separators

         COALESCE('$' + stuff(convert(VARCHAR,[1],1),charindex('.',

convert(VARCHAR,[1],1)),3,''),'') AS 'Q1',

         COALESCE('$' + stuff(convert(VARCHAR,[2],1),charindex('.',

convert(VARCHAR,[2],1)),3,''),'') AS 'Q1',

         COALESCE('$' + stuff(convert(VARCHAR,[3],1),charindex('.',

convert(VARCHAR,[3],1)),3,''),'') AS 'Q3',

         COALESCE('$' + stuff(convert(VARCHAR,[4],1),charindex('.',

convert(VARCHAR,[4],1)),3,''),'') AS 'Q4'

FROM     cteQuarterBigOrders

         PIVOT

         (SUM(OrderAmount)

          FOR Quarter IN ( [1],[2],[3],[4] ) ) AS P

ORDER BY ProductName

GO

-- Results