Writing the base query takes a bit of planning.
This syntax might look intimidating, but I'll guide you through it step by step. As you can see, there are two main parts: a base query (callout A) and a PIVOT expression (callout B). Listing 2 shows the syntax for a PIVOT query that uses a CTE. What BOL doesn't point out is that you can use a common table expression (CTE) instead. The syntax in BOL uses a derived table as the basis for the PIVOT query. Note that I won't be using the PIVOT syntax shown in SQL Server Books Online (BOL) because that syntax can be difficult to understand at first glance.
#Pivot table tool constraints how to#
I'll explain how to write queries that use the PIVOT operator to produce the results shown in Table 2. In Table 2, the data is summarized and displayed so that the months can be easily compared from year to year. Table 2 shows the results as you would like to see them. Table 1: Partial Results from the Query in Listing 1 ORDER BY YEAR(OrderDate),MONTH(OrderDate) GROUP BY YEAR(OrderDate),MONTH(OrderDate) SELECT SUM(TotalDue) TotalDue, YEAR(OrderDate) AS YearOrdered, As you can see, looking for trends by month isn't easy. Table 1 shows an excerpt from the results. Using the 2005 or 2008 version of the AdventureWorks database, you can create a query summarizing the data with the code in Listing 1. For example, suppose you want to create a report that breaks down sales by year and month so that you can compare sales months for different years. The PIVOT operator, which was introduced in SQL Server 2005, lets you create results that are pivoted, essentially using the data from one of the columns as column headers.
0 kommentar(er)
