One of the most requested pieces of code on the Internet forums about SQL Server these days is the code for making a crosstab query. A crosstab query is a type of query that displays data in a matrix format, with rows representing one set of data and columns representing another set of data. This type of query is often used to summarize data and provide a more organized view of the information.

In earlier versions of SQL Server, there was no native support for crosstab queries. However, starting from SQL Server 2005, Microsoft introduced support for crosstab queries using the PIVOT operator. The PIVOT operator allows you to rotate rows into columns, making it easier to create crosstab queries.

Here is an example of a crosstab query using the PIVOT operator in SQL Server 2005:

USE MyDatabase
GO
WITH ProductSales(ProductID, OrderYear, OrderTotal)
 AS (
      SELECT
         det.productID,
         YEAR(hdr.orderdate),
         det.linetotal
      FROM sales.salesorderdetail det
      JOIN sales.salesorderheader hdr
        ON det.salesorderid = hdr.salesorderid
)
SELECT
    ProductSalesPivot.productID,
    Total_Sales_2001 = ISNULL([2001], 0),
    Total_Sales_2002 = ISNULL([2002], 0),
    Total_Sales_2003 = ISNULL([2003], 0),
    Total_Sales_2004 = ISNULL([2004], 0)
FROM ProductSales
PIVOT (SUM(OrderTotal)
       FOR OrderYear IN ([2001], [2002], [2003], [2004])
) AS ProductSalesPivot
ORDER BY ProductSalesPivot.ProductID

In this example, the query calculates the total sales for each product in different years. The PIVOT operator rotates the OrderYear values into columns, making it easier to compare the sales across different years.

However, it’s important to note that in SQL Server 2005, you still had to hardwire the columns using the keyword CASE. This means that you needed to know the number of columns in advance, which could be a limitation in some scenarios.

In later versions of SQL Server, such as SQL Server 2008 and onwards, Microsoft introduced dynamic pivot queries, which allow you to create crosstab queries without knowing the number of columns in advance. Dynamic pivot queries use dynamic SQL to generate the necessary columns based on the data.

Here is an example of a dynamic pivot query in SQL Server:

USE MyDatabase
SELECT OfficeName,
       SUM(CASE WHEN MONTH(OrderDate) = 1 THEN OrderAmount ELSE 0 END) AS 'January',
       SUM(CASE WHEN MONTH(OrderDate) = 2 THEN OrderAmount ELSE 0 END) AS 'February',
       SUM(CASE WHEN MONTH(OrderDate) = 3 THEN OrderAmount ELSE 0 END) AS 'March',
       SUM(CASE WHEN MONTH(OrderDate) = 4 THEN OrderAmount ELSE 0 END) AS 'April',
       SUM(CASE WHEN MONTH(OrderDate) = 5 THEN OrderAmount ELSE 0 END) AS 'May',
       SUM(CASE WHEN MONTH(OrderDate) = 6 THEN OrderAmount ELSE 0 END) AS 'June',
       SUM(CASE WHEN MONTH(OrderDate) = 7 THEN OrderAmount ELSE 0 END) AS 'July',
       SUM(CASE WHEN MONTH(OrderDate) = 8 THEN OrderAmount ELSE 0 END) AS 'August',
       SUM(CASE WHEN MONTH(OrderDate) = 9 THEN OrderAmount ELSE 0 END) AS 'September',
       SUM(CASE WHEN MONTH(OrderDate) = 10 THEN OrderAmount ELSE 0 END) AS 'October',
       SUM(CASE WHEN MONTH(OrderDate) = 11 THEN OrderAmount ELSE 0 END) AS 'November',
       SUM(CASE WHEN MONTH(OrderDate) = 12 THEN OrderAmount ELSE 0 END) AS 'December'
FROM MyTable
WHERE YEAR(OrderDate) = 2006
GROUP BY OfficeName
ORDER BY OfficeName

In this example, the query calculates the total order amount for each month in the year 2006, grouped by office name. The dynamic pivot is achieved by using the CASE statement to conditionally sum the order amount based on the month of the order date.

Dynamic pivot queries provide more flexibility and allow you to create crosstab queries without knowing the number of columns in advance. However, they can be more complex to write and maintain compared to static pivot queries.

It’s important to note that crosstab queries can be resource-intensive and may not be suitable for large datasets. It’s recommended to use them judiciously and consider alternative approaches, such as using reporting tools or creating views, depending on your specific requirements.

In conclusion, SQL Server provides support for creating crosstab queries using the PIVOT operator. Starting from SQL Server 2008, dynamic pivot queries allow you to create crosstab queries without knowing the number of columns in advance. Understanding how to create crosstab queries can be a valuable skill for SQL Server developers and can help in organizing and summarizing data effectively.

Thank you for reading!