{"id":1918,"date":"2015-01-01T00:00:00","date_gmt":"2014-12-04T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/questions-about-pivoting-data-in-sql-server-you-were-too-shy-to-ask\/"},"modified":"2021-09-29T16:21:31","modified_gmt":"2021-09-29T16:21:31","slug":"questions-about-pivoting-data-in-sql-server-you-were-too-shy-to-ask","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/questions-about-pivoting-data-in-sql-server-you-were-too-shy-to-ask\/","title":{"rendered":"SQL Server PIVOT: 12 Questions Answered (Multi-Column, Dynamic, UNPIVOT)"},"content":{"rendered":"
\n
    \n
  1. “How do I use the PIVOT operator to rotate data in SQL Server?”<\/a><\/li>\n
  2. “Can I specify column names different from the values in the pivoted column?”<\/a><\/li>\n
  3. “I’m confused by how data is grouped when using the PIVOT operator. Can I control grouping?”<\/a><\/li>\n
  4. “How do I avoid grouping on a column’s values when pivoting data so I can return a single-row result set that aggregates the entire dataset?”<\/a><\/li>\n
  5. “SQL Server does not permit the use of column expressions in the PIVOT clause. Is there a way to get around this limitation?”<\/a><\/li>\n
  6. “Can I group data by more than one column when I use the PIVOT operator? “<\/a><\/li>\n
  7. “Is there a way to work around SQL Server’s restriction against using COUNT(*) in the PIVOT clause?”<\/a><\/li>\n
  8. “Is it possible to pivot more than one column when pivoting data in SQL Server?”<\/a><\/li>\n
  9. “I sometimes run queries against a SQL Server 2000 database, which doesn’t support the PIVOT operator. How do I pivot data without using PIVOT?”<\/a><\/li>\n
  10. “Does the PIVOT operator let you perform multiple aggregations?”<\/a><\/li>\n
  11. “How do I pivot data if I don’t know the values in the pivoted column?”<\/a><\/li>\n
  12. “How do I unpivot data in SQL Server?”<\/a><\/li>\n<\/ol>\n

    “How do I use the PIVOT<\/code> operator to rotate data in SQL Server?”<\/h3>\n

    You use the PIVOT<\/code><\/b> operator within your query’s FROM<\/code><\/b> clause to rotate and aggregate the values in a dataset. The data is pivoted based on one of the columns in the dataset. Each unique value in that column becomes its own column, which contains aggregated pivoted data.<\/p>\n

    To better understand how this all works, let’s start with the basic syntax for a query that uses the PIVOT<\/code><\/b> operator:<\/p>\n

    SELECT column_list\r\nFROM table_expression\r\n\u00a0 PIVOT\r\n\u00a0 (\r\n\u00a0\u00a0\u00a0 aggregate_function(aggregate_column)\r\n\u00a0\u00a0\u00a0 FOR pivot_column\r\n\u00a0\u00a0\u00a0 IN( pivot_column_values )\r\n\u00a0 ) [AS] pivot_table_alias\r\n[ORDER BY column_list];\r\n\r\n<\/pre>\n
    For the  SELECT<\/code><\/b> clause, you can specify an asterisk (*) or the individual columns, and for the  FROM<\/code><\/b> clause, you can specify a table or table expression. If you use a table expression, then you must also define a table alias. You can also include an ORDER<\/code><\/b> BY<\/code><\/b> clause, but that’s optional. You’ll get to see these clauses in action as we progress through the questions. For now, let’s focus on the PIVOT<\/code><\/b> clause. You need to understand how this clause works to make sure your pivots work the way you want.<\/p>\n
    After you specify the PIVOT<\/code><\/b> keyword, you pass in what are essentially three arguments, enclosed in parentheses. The first is the aggregate function and the name of the column to be aggregated. You can use any aggregate function except the  COUNT<\/code><\/b> function, when used with an asterisk, as in COUNT(*)<\/code><\/b>.<\/p>\n
    Next, you define the FOR<\/code><\/b> subclause, which specifies the column on which the pivot will be based. It is this column whose distinct values are turned into their own columns. The FOR<\/code><\/b> subclause also includes the  IN<\/code><\/b> operator, which is where you specify the pivot column values that will be turned into columns. The values you specify here must exist in the pivot column or they will be ignored.<\/p>\n
    This will all make more sense when you see it in action. Let’s start with the following temporary table and data:<\/p>\n
    CREATE TABLE #BookSales\r\n(BookType VARCHAR(20), SalesYear INT, BookSales MONEY);\r\nGO\r\n\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 11201);\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 12939);\r\nINSERT INTO #BookSales VALUES('Fiction', 2013, 10436);\r\nINSERT INTO #BookSales VALUES('Fiction', 2013, 9346);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, 7214);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, 5800);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 8922);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 7462);\r\n\r\nSELECT * FROM #BookSales;\r\n<\/pre>\n
    The  SELECT<\/code><\/b> statement returns the following results, which include sales totals for the two book types over the course of two years:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    SalesYear<\/b><\/p>\n<\/td>\n
    		\n
    BookSales<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2014<\/p>\n<\/td>\n
    		\n
    11201.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2014<\/p>\n<\/td>\n
    		\n
    12939.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2013<\/p>\n<\/td>\n
    		\n
    10436.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2013<\/p>\n<\/td>\n
    		\n
    9346.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    2014<\/p>\n<\/td>\n
    		\n
    7214.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    2014<\/p>\n<\/td>\n
    		\n
    5800.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    2013<\/p>\n<\/td>\n
    		\n
    8922.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    2013<\/p>\n<\/td>\n
    		\n
    7462.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Although this is a very simple dataset, it gives us what we need to demonstrate the PIVOT<\/code><\/b> operator. In the following  SELECT<\/code><\/b> statement, I use the operator to turn the SalesYear<\/code><\/b> values into columns and to pivot and aggregate the sales totals:<\/p>\n
    SELECT *\r\nFROM #BookSales\r\n\u00a0 PIVOT(SUM(BookSales) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n\t<\/pre>\n
    If you refer back to the syntax above, you can see that BookSales<\/code><\/b> is the aggregate column,  SalesYear<\/code><\/b> is the pivot column, and the  SalesYear<\/code><\/b> values, which are passed into the IN<\/code><\/b> operator, become the new columns. The following table shows the results returned by the SELECT<\/code><\/b> statement:<\/p>\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    19782.00<\/p>\n<\/td>\n
    		\n
    24140.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    16384.00<\/p>\n<\/td>\n
    		\n
    13014.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    An important point to note about the results is that the first column is BookType<\/code><\/b>, with a row for fiction sales and a row for nonfiction sales. The  BookSales<\/code><\/b> values are aggregated according to year, with the data grouping based on the  BookType<\/code><\/b> column. SQL Server automatically grouped the data by the values in this column, even though the column is not explicitly included in the PIVOT clause. (We’ll cover grouping more in depth in a separate question.)<\/p>\n
    In the preceding example, I used the  SUM<\/code><\/b> function to aggregate the data, but we can use a different aggregate function. For example, the following PIVOT<\/code><\/b> clause uses the AVG<\/code><\/b> function:<\/p>\n
    SELECT *\r\nFROM #BookSales\r\n\u00a0 PIVOT(AVG(BookSales) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n\t<\/pre>\n
    This time, the SELECT<\/code><\/b> statement returns sales averages, rather than sales totals, as shown in the following table:<\/p>\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    9891.00<\/p>\n<\/td>\n
    		\n
    12070.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    8192.00<\/p>\n<\/td>\n
    		\n
    6507.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    In the previous two examples, the  IN<\/code><\/b> operator in the PIVOT<\/code><\/b> clause specified all the  SalesYear<\/code><\/b> values, but you can specify whatever values you want to return, as long as they’re included in the pivoted column. For example, the  IN<\/code><\/b> operator in the following PIVOT<\/code><\/b> clause includes only the 2014<\/code><\/b> value:<\/p>\n
    SELECT *\r\nFROM #BookSales\r\n\u00a0 PIVOT(SUM(BookSales) \r\n\u00a0 FOR SalesYear IN([2014])\r\n\u00a0 ) AS PivotSales;\r\n<\/pre>\n
    As the following table shows, the results now include only two columns:<\/p>\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    24140.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    13014.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    There is, of course, more to the  PIVOT<\/code><\/b> operator than what’s shown here, but this should give you the basics. The key to understanding the operator is in knowing which column to aggregate, which column to pivot, and which one should be implicitly grouped.<\/p>\n
    “Can I specify column names different from the values in the pivoted column?”<\/h3>\n
    Yes, you can, by using column aliases, but before we get into that, let’s return to the syntax I introduced in the previous question:<\/p>\n
    SELECT column_list\r\nFROM table_expression\r\n\u00a0 PIVOT\r\n\u00a0 (\r\n\u00a0\u00a0\u00a0 aggregate_function(aggregate_column)\r\n\u00a0\u00a0\u00a0 FOR pivot_column\r\n\u00a0\u00a0\u00a0 IN( pivot_column_values )\r\n\u00a0 ) [AS] pivot_table_alias\r\n[ORDER BY column_list];\r\n\r\n<\/pre>\n
    Notice that I use the column_list<\/code><\/i> placeholder in the SELECT<\/code><\/b> clause. Before we dig any deeper into the select list, let’s again start with the #BookSales<\/code><\/b> temporary table and populate it:<\/p>\n
    CREATE TABLE #BookSales\r\n(BookType VARCHAR(20), SalesYear INT, BookSales MONEY);\r\nGO\r\n\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 11201);\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 12939);\r\nINSERT INTO #BookSales VALUES('Fiction', 2013, 10436);\r\nINSERT INTO #BookSales VALUES('Fiction', 2013, 9346);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, 7214);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, 5800);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 8922);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 7462);\r\n<\/pre>\n
    As you saw in the preceding examples, if we want to go with the default column names produced by the PIVOT<\/code><\/b> operator, we can use just an asterisk in the SELECT<\/code><\/b> clause:<\/p>\n
    SELECT *\r\nFROM #BookSales\r\n\u00a0 PIVOT(SUM(BookSales) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n<\/pre>\n
    As expected, the query returns the data shown in the following table:<\/p>\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    19782.00<\/p>\n<\/td>\n
    		\n
    24140.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    16384.00<\/p>\n<\/td>\n
    		\n
    13014.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
     <\/p>\n
    Suppose we now want to better control how the columns are returned in the result set. Let’s revise our syntax a bit, with more detail in the  SELECT<\/code><\/b> clause:<\/p>\n
    SELECT \r\n\u00a0 group_column [ [AS] column_alias ],\r\n\u00a0 pivoted_column_1 [ [AS] column_alias ],\r\n\u00a0 pivoted_column_2 [ [AS] column_alias ],\r\n\u00a0 ...\r\n\u00a0 pivoted_column_last [ [AS] column_alias ]\r\nFROM table_expression\r\n\u00a0 PIVOT\r\n\u00a0 (\r\n\u00a0\u00a0\u00a0 aggregate_function(aggregate_column)\r\n\u00a0\u00a0\u00a0 FOR pivot_column\r\n\u00a0\u00a0\u00a0 IN( pivot_column_values )\r\n\u00a0 ) [AS] pivot_table_alias\r\n[ORDER BY column_list];\r\n\r\n<\/pre>\n
    By default, the first column in the select list is the column on which the aggregated grouping is based. This is the column that you do not<\/i> specify in the PIVOT<\/code><\/b> clause. The subsequent columns are based on the unique values in the pivoted column. If we were to repeat the previous example, but include the column names in the select list, our query would look something like the following:<\/p>\n
    SELECT BookType, 2013, 2014\r\nFROM #BookSales\r\n\u00a0 PIVOT(SUM(BookSales) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n\t<\/pre>\n
    All I’ve done here is specify the column names rather than use an asterisk. However, because we’re dealing with integers for some of the column names, the results we get are not what we might expect:<\/p>\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    (No column name)<\/b><\/p>\n<\/td>\n
    		\n
    (No column name)<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2013<\/p>\n<\/td>\n
    		\n
    2014<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    2013<\/p>\n<\/td>\n
    		\n
    2014<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    In this case, the values 2013<\/code><\/b> and 2014<\/code><\/b> are treated as literals and returned as column values, rather than column names. We can easily remedy this by delimiting the column names:<\/p>\n
    SELECT BookType, [2013], [2014]\r\nFROM #BookSales\r\n\u00a0 PIVOT(SUM(BookSales) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n\t<\/pre>\n
    Now we get the results we would expect:<\/p>\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    19782.00<\/p>\n<\/td>\n
    		\n
    24140.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    16384.00<\/p>\n<\/td>\n
    		\n
    13014.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Of course, all we’ve achieved here is to return the same results we would have achieved if we had used an asterisk. However, we can use the select list to specify the column names in a different order than how the columns are listed in the  IN<\/code><\/b> operator within the  FOR<\/code><\/b> subclause:<\/p>\n
    SELECT BookType, [2014], [2013]\r\nFROM #BookSales\r\n\u00a0 PIVOT(SUM(BookSales) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n<\/pre>\n
    Now the aggregated data is listed in an order different from the previous examples:<\/p>\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n
    		\n
    2013<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    24140.00<\/p>\n<\/td>\n
    		\n
    19782.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    13014.00<\/p>\n<\/td>\n
    		\n
    16384.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    We can in fact, even change the position of the group column, which in this case is BookType<\/code><\/b>:<\/p>\n
    SELECT [2013], BookType, [2014]\r\nFROM #BookSales\r\n\u00a0 PIVOT(SUM(BookSales) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n<\/pre>\n
    In most cases, though, you’ll want to keep the group column in the first position, or you end up with results like those shown in the following table:<\/p>\n\n\n\n\n\n
    \n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    19782.00<\/p>\n<\/td>\n
    		\n
    Fiction<\/p>\n<\/td>\n
    		\n
    24140.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    16384.00<\/p>\n<\/td>\n
    		\n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    13014.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Clearly, moving the group column around in this way can make the results less readable. Imagine what it would be like if you were dealing with 10 times the number of columns.<\/p>\n
    Another advantage of being able to specify the columns in the select list is that you can assign aliases to them. In the next example, I’ve assigned an alias to each of the three columns:<\/p>\n
    SELECT BookType AS BookCategory, \r\n\u00a0 [2013] AS Year2013, [2014] AS Year2013\r\nFROM #BookSales\r\n\u00a0 PIVOT(SUM(BookSales) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n<\/pre>\n
    As you’d expect, the column aliases are reflected in the query results:<\/p>\n\n\n\n\n\n
    \n
     BookCategory<\/b><\/p>\n<\/td>\n
    		\n
    Year2013<\/b><\/p>\n<\/td>\n
    		\n
    Year2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    19782.00<\/p>\n<\/td>\n
    		\n
    24140.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    16384.00<\/p>\n<\/td>\n
    		\n
    13014.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Whenever you find that the values in your pivot column do not make good column names for the pivoted data, you can easily assign aliases to them in this way to make your data more readable.<\/p>\n
    “I’m confused by how data is grouped when using the PIVOT operator. Can I control grouping?”<\/h3>\n
    The  PIVOT<\/code><\/b> operator is a bit odd in the fact that data is grouped implicitly based on the columns you do not<\/i> specify in the PIVOT<\/code><\/b> operator. Let’s return to our example temporary table:<\/p>\n
    CREATE TABLE #BookSales\r\n(BookType VARCHAR(20), SalesYear INT, BookSales MONEY);\r\nGO\r\n\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 11201);\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 12939);\r\nINSERT INTO #BookSales VALUES('Fiction', 2013, 10436);\r\nINSERT INTO #BookSales VALUES('Fiction', 2013, 9346);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, 7214);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, 5800);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 8922);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 7462);\r\n\r\nNow let's once again use the PIVOT to rotate the data based on the SalesYear column:\r\nSELECT *\r\nFROM #BookSales\r\n\u00a0 PIVOT(SUM(BookSales) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n\t<\/pre>\n
    In this case, the source dataset is a simple three-column table. BookSales<\/code><\/b> serves as the aggregate column, and  SalesYear<\/code><\/b> serves as the pivot column. That leaves BookType<\/code><\/b>, which automatically becomes the group column, as shown in the following results:<\/p>\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    19782.00<\/p>\n<\/td>\n
    		\n
    24140.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    16384.00<\/p>\n<\/td>\n
    		\n
    13014.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    In the real world, our source datasets are seldom that simple. Suppose, for example, our temp table included an additional column, a simple nullable  BIT<\/code><\/b> column that indicates whether the totals have been verified, with  1<\/code><\/b> meaning YES<\/code><\/b>, 0<\/code><\/b> meaning NO<\/code><\/b>, and NULL<\/code><\/b> meaning we don’t know one way or the other:<\/p>\n
    CREATE TABLE #BookSales\r\n(BookType VARCHAR(20), SalesYear INT, BookSales MONEY,\r\nVerified BIT);\r\nGO\r\n\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 11201, 1);\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 12939, 1);\r\nINSERT INTO #BookSales VALUES('Fiction', 2013, 10436, NULL);\r\nINSERT INTO #BookSales VALUES('Fiction', 2013, 9346, 0);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, 7214, 0);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, 5800, 1);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 8922, NULL);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 7462, 0);\r\n<\/pre>\n
     <\/p>\n
    Chances are, you might not actually make such a column nullable, preferring instead to default to 0<\/code><\/b>, but this is good enough for now to demonstrate a point, that is, grouping is based on all<\/i> columns not specified in the PIVOT<\/code><\/b> clause. Let’s try it out. The following SELECT<\/code><\/b> statement is identical to the one in the preceding example:<\/p>\n
    SELECT *\r\nFROM #BookSales\r\n\u00a0 PIVOT(SUM(BookSales) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n<\/pre>\n
    But now our results are quite different, skewing them in a direction we likely do not want to go:<\/p>\n\n\n\n\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    Verified<\/b><\/p>\n<\/td>\n
    		\n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n
    		\n
    10436.00<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n
    		\n
    8922.00<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n
    		\n
    9346.00<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n
    		\n
    7462.00<\/p>\n<\/td>\n
    		\n
    7214.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n
    		\n
    24140.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n
    		\n
    5800.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The database engine has grouped the data by both the BookType<\/code><\/b> and Verified<\/code><\/b> columns, rather than just  BookType<\/code><\/b>. To get around this, we need to specify a table expression in our FROM<\/code><\/b> clause that retrieves only the relevant columns:<\/p>\n
    SELECT *\r\nFROM \r\n\u00a0 (SELECT BookType, SalesYear, BookSales \r\n\u00a0\u00a0 FROM #BookSales) Sales\r\n\u00a0 PIVOT(SUM(BookSales) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n<\/pre>\n
    Now we get the results we expect, with the data grouped only by the BookType<\/code><\/b> table:<\/p>\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    19782.00<\/p>\n<\/td>\n
    		\n
    24140.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    16384.00<\/p>\n<\/td>\n
    		\n
    13014.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    In most cases, you’ll find you’ll need to use a table expression in the FROM<\/code><\/b> clause, rather than specifying only the base table. In fact, you’ll often find that most of the real engineering for pivoting data rests with the table expression.<\/p>\n
    “How do I avoid grouping on a column’s values when pivoting data so I can return a single-row result set that aggregates the entire dataset?”<\/h3>\n
    The trick to using PIVOT<\/code><\/b> operator to aggregate and rotate data effectively often rests with getting the grouping right. This idea also applies to aggregating the entire dataset. Let’s return again to our basic three-column temporary table and perform a simple pivot:<\/p>\n
    CREATE TABLE #BookSales\r\n(BookType VARCHAR(20), SalesYear INT, BookSales MONEY);\r\nGO\r\n\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 11201);\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 12939);\r\nINSERT INTO #BookSales VALUES('Fiction', 2013, 10436);\r\nINSERT INTO #BookSales VALUES('Fiction', 2013, 9346);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, 7214);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, 5800);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 8922);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 7462);\r\n\r\nSELECT *\r\nFROM #BookSales\r\n\u00a0 PIVOT(SUM(BookSales) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n\t<\/pre>\n
    As expected, our query returns the results in the following table:<\/p>\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    19782.00<\/p>\n<\/td>\n
    		\n
    24140.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    16384.00<\/p>\n<\/td>\n
    		\n
    13014.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    But suppose we don’t want to group the data by the BookType<\/code><\/b> column and instead want to return the aggregated values for the entire year. For that, we need the right table expression, one that returns only the  BookSales<\/code><\/b> and SalesYear<\/code><\/b> columns:<\/p>\n
    SELECT *\r\nFROM\r\n\u00a0 (SELECT BookSales, SalesYear \r\n\u00a0\u00a0 FROM #BookSales) Sales\r\n\u00a0 PIVOT(SUM(BookSales) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n<\/pre>\n
    As before, we’ve specified BookSales<\/code><\/b> table as our aggregate column and SalesYear<\/code><\/b> as our pivot column. However, because these are now the only two columns in our dataset, we no longer have a third column to serve as the group column. As a result, our query returns the following data:<\/p>\n\n\n\n\n
    \n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    36166.00<\/p>\n<\/td>\n
    		\n
    37154.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    We now have totals for each year, without the data being grouped by any one column. We can, however, specify a column in the first position that provides context to the other data:<\/p>\n
    SELECT 'Total Sales' AS [Year],\r\n\u00a0 [2013], [2014]\r\nFROM\r\n\u00a0 (SELECT BookSales, SalesYear \r\n\u00a0\u00a0 FROM #BookSales) Sales\r\n\u00a0 PIVOT(SUM(BookSales) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n<\/pre>\n
    In this case, I’ve created a column named  Year<\/code><\/b> and assigned the value Total<\/code><\/b> Sales<\/code><\/b> to that column, as shown in the following results:<\/p>\n\n\n\n\n
    \n
    Year<\/b><\/p>\n<\/td>\n
    		\n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Total Sales<\/p>\n<\/td>\n
    		\n
    36166.00<\/p>\n<\/td>\n
    		\n
    37154.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    This can be a handy strategy to make it easier to understand the data, but you need to be careful because the column name can be a little deceiving in that it actually points to the other column names, rather than the value in its own column. Be sure to give careful consideration to what you’re doing if you go down this route.<\/p>\n
    “SQL Server does not permit the use of column expressions in the PIVOT clause. Is there a way to get around this limitation?”<\/h3>\n
    Like many of the PIVOT<\/code><\/b> clause limitations, the way to get around the restriction against column expressions is to use the table expression in the FROM<\/code><\/b> clause. There you can create whatever expressions you need. Let’s start with a different variation of our #BookSales<\/code><\/b> temporary table:<\/p>\n
    CREATE TABLE #BookSales\r\n(BookType VARCHAR(20), SalesDate DATETIME, \r\n\u00a0 BookSales MONEY, BookCost MONEY);\r\nGO\r\n\r\nINSERT INTO #BookSales VALUES\r\n\u00a0 ('Fiction', '2014-12-23 15:11:12.017', 11201, 9043),\r\n\u00a0 ('Fiction', '2014-05-23 15:11:12.017', 12939, 8578),\r\n\u00a0 ('Fiction', '2013-11-23 15:11:12.017', 10436, 9843),\r\n\u00a0 ('Fiction', '2013-03-23 15:11:12.017', 9346, 7743),\r\n\u00a0 ('Nonfiction', '2014-09-23 15:11:12.017', 7214, 5677),\r\n\u00a0 ('Nonfiction', '2014-06-23 15:11:12.017', 5800, 4301),\r\n\u00a0 ('Nonfiction', '2013-12-23 15:11:12.017', 8922, 6895),\r\n\u00a0 ('Nonfiction', '2013-04-23 15:11:12.017', 7462, 6873);\r\n\r\n<\/pre>\n
    We now have a DATETIME<\/code><\/b> column, rather than a simple INT<\/code><\/b> column for the year, and we’ve added a column for the wholesale cost. Suppose we now want to calculate the net sales and extract only the year for our pivot. We can create a query similar to the following:<\/p>\n
    SELECT *\r\nFROM \r\n\u00a0 (SELECT BookType AS BookCategory,\r\n\u00a0\u00a0\u00a0 (BookSales - BookCost) AS NetSales,\r\n\u00a0\u00a0\u00a0 YEAR(SalesDate) AS SalesYear\r\n\u00a0 FROM #BookSales) AS Sales\r\n\u00a0 PIVOT(SUM(NetSales) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n\r\n<\/pre>\n
    Once again, our table expression is doing the work for us by extracting the year and calculating the net sale. We then use the name of the computed columns in our PIVOT<\/code><\/b> clause, giving us the results shown in the following table:<\/p>\n\n\n\n\n\n
    \n
     BookCategory<\/b><\/p>\n<\/td>\n
    		\n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2196.00<\/p>\n<\/td>\n
    		\n
    6519.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    2616.00<\/p>\n<\/td>\n
    		\n
    3036.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Although this is a simple example, it demonstrates the importance of getting the table expression right so your  PIVOT<\/code><\/b> operator has exactly the data it needs to do its magic.<\/p>\n
    “Can I group data by more than one column when I use the PIVOT operator?”<\/h3>\n
    Yes, you can group multiple columns, but you need to be sure you’re grouping only those columns that should be grouped. As mentioned earlier, the  PIVOT<\/code><\/b> operator groups data by those columns in the data set that are not specified as the aggregate or pivot columns. To demonstrate multi-column grouping, let’s recast our #BookSales<\/code><\/b> temp table once again:<\/p>\n
    CREATE TABLE #BookSales\r\n(BookType VARCHAR(20), BookSubtype VARCHAR(20), \r\n\u00a0 SalesYear INT, BookSales MONEY, Verified BIT);\r\nGO\r\n\r\nINSERT INTO #BookSales VALUES('Fiction', 'Adults', 2014, 11201, 1);\r\nINSERT INTO #BookSales VALUES('Fiction', 'Children', 2014, 12939, 0);\r\nINSERT INTO #BookSales VALUES('Fiction', 'Adults', 2013, 10436, 1);\r\nINSERT INTO #BookSales VALUES('Fiction', 'Children', 2013, 9346, 0);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 'Adults', 2014, 7214, 0);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 'Children', 2014, 5800, 1);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 'Adults', 2013, 8922, 1);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 'Children', 2013, 7462, 0);\r\nINSERT INTO #BookSales VALUES('Fiction', 'Adults', 2014, 14209, 0);\r\nINSERT INTO #BookSales VALUES('Fiction', 'Children', 2014, 11489, 0);\r\nINSERT INTO #BookSales VALUES('Fiction', 'Adults', 2013, 9909, 1);\r\nINSERT INTO #BookSales VALUES('Fiction', 'Children', 2013, 8726, 0);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 'Adults', 2014, 4399, 1);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 'Children', 2014, 5248, 0);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 'Adults', 2013, 7740, 0);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 'Children', 2013, 8267, 1);\r\nINSERT INTO #BookSales VALUES('Fiction', 'YA', 2014, 9854, 0);\r\nINSERT INTO #BookSales VALUES('Fiction', 'YA', 2013, 8756, 1);\r\n\r\n<\/pre>\n
    This time we’re also including the  BookSubtype<\/code><\/b> column to provide an additional way to categorize the data. We can then include the column in our table expression, as shown in the following example:<\/p>\n
    SELECT * \r\nFROM \r\n\u00a0 (SELECT BookType, BookSubtype, SalesYear, BookSales\r\n\u00a0\u00a0\u00a0 FROM #BookSales) AS Sales\r\n\u00a0 PIVOT(SUM(BookSales) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales\r\nORDER BY BookType, BookSubtype;\r\n\r\n<\/pre>\n
    The table expression defines our dataset, which includes the BookSubtype<\/code><\/b> column. However, as in the previous examples, the PIVOT<\/code><\/b> clause specifies only the BookSales<\/code><\/b> and SalesYear<\/code><\/b> columns, which means the data will be grouped by the BookType<\/code><\/b> and BookSubtype<\/code><\/b> columns when it is pivoted, as shown in the following results:<\/p>\n\n\n\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
     BookSubtype<\/b><\/p>\n<\/td>\n
    		\n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    Adults<\/p>\n<\/td>\n
    		\n
    20345.00<\/p>\n<\/td>\n
    		\n
    25410.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    Children<\/p>\n<\/td>\n
    		\n
    18072.00<\/p>\n<\/td>\n
    		\n
    24428.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    YA<\/p>\n<\/td>\n
    		\n
    8756.00<\/p>\n<\/td>\n
    		\n
    9854.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    Adults<\/p>\n<\/td>\n
    		\n
    16662.00<\/p>\n<\/td>\n
    		\n
    11613.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    Children<\/p>\n<\/td>\n
    		\n
    15729.00<\/p>\n<\/td>\n
    		\n
    11048.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Once again, the key to making this all work is to ensure that you get your table expression right so that you’re controlling the columns on which you group the data.<\/p>\n
    “Is there a way to work around SQL Server’s restriction against using COUNT(*) in the PIVOT clause?”<\/h3>\n
    It’s true that you can’t use the  COUNT<\/code><\/b> aggregate function with an asterisk in your PIVOT<\/code><\/b> clause, but you can use the function if you specify a one of the columns in the dataset. To demonstrate this, let’s create our temporary table once more:<\/p>\n
    CREATE TABLE #BookSales\r\n(BookType VARCHAR(20), SalesYear INT, BookSales MONEY);\r\nGO\r\n\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 11201);\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 12939);\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 9856);\r\nINSERT INTO #BookSales VALUES('Fiction', 2013, 10436);\r\nINSERT INTO #BookSales VALUES('Fiction', 2013, 9346);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, 7214);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, 5800);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 8922);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 7462);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 4372);\r\n\t<\/pre>\n
    Suppose that we now want to return the number of entries, rather than the total number of sales. One way we can do this is to pass the  BookSales<\/code><\/b> column in as an argument to the COUNT<\/code><\/b> function:<\/p>\n
    SELECT *\r\nFROM #BookSales\r\n\u00a0 PIVOT(COUNT(BookSales) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n<\/pre>\n
    The statement will now return a count, rather than a sale amount, as shown in the following table:<\/p>\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Although this appears to work, the challenge with this approach is that it assumes the specified column is non-nullable, that is, does not contain  NULL<\/code><\/b> values. But that’s not always the case. For example, suppose we insert the following two rows to serve as placeholders during the load process:<\/p>\n
    INSERT INTO #BookSales VALUES('Fiction', 2014, NULL);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, NULL);\r\n<\/pre>\n
    If we were to rerun the previous  SELECT<\/code><\/b> statement, we would receive the same results, despite the fact that we’ve added two rows. That’s because the COUNT<\/code><\/b> function ignores NULL<\/code><\/b> values. Another approach we might consider is to pass the SalesYear<\/code><\/b> column into the function:<\/p>\n
    SELECT *\r\nFROM #BookSales\r\n\u00a0 PIVOT(COUNT(SalesYear) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n<\/pre>\n
    Unfortunately, this confuses matters even more because the SalesYear<\/code><\/b> column is also the pivot column, giving us the following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    BookSales<\/b><\/p>\n<\/td>\n
    		\n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    NULL<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    4372.00<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    5800.00<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    7214.00<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    7462.00<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    8922.00<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    9346.00<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    9856.00<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    10436.00<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    11201.00<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    12939.00<\/p>\n<\/td>\n
    		\n
    0<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Because we left the BookSales<\/code><\/b> column out of the PIVOT<\/code><\/b> clause, it’s now treated as a group column. One way around this is to create a table expression in the FROM<\/code><\/b> clause so we return only the  BookType<\/code><\/b> and SalesYear<\/code><\/b> columns:<\/p>\n
    SELECT *\r\nFROM\r\n\u00a0 (SELECT BookType, SalesYear\r\n\u00a0\u00a0 FROM #BookSales) AS Sales\r\n\u00a0 PIVOT(COUNT(SalesYear) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n\t<\/pre>\n
    This time we get the results we want:<\/p>\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n
    		\n
    4<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Unfortunately, with this approach we’re once again assuming that the column will contain no NULL<\/code><\/b> values. A safer approach might be to add a column to our dataset that contains the same constant for every row:<\/p>\n
    SELECT *\r\nFROM\r\n\u00a0 (SELECT BookType, SalesYear,\r\n\u00a0\u00a0\u00a0 1 AS CntCol\r\n\u00a0\u00a0 FROM #BookSales) AS Sales\r\n\u00a0 PIVOT(COUNT(CntCol) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n\t<\/pre>\n
    In this case, I’ve created a column name  CntCol<\/code><\/b> and set its value to 1<\/code><\/b>. This way, I don’t have to worry about  NULL<\/code><\/b> values skewing the results. The following table shows the data returned by  SELECT<\/code><\/b> statement:<\/p>\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n
    		\n
    4<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Adding a column in this way is a handy trick to know when you want to return reliable counts in your pivoted data and you can’t guarantee that the aggregate column will contain no NULL<\/code><\/b> values..<\/p>\n
    “Is it possible to pivot more than one column when pivoting data in SQL Server?”<\/h3>\n
    Yes, it is possible to base your pivot on more than one column; however, you can’t do this within the PIVOT<\/code><\/b> clause. As with other PIVOT<\/code><\/b> limitations, you must use your table expression to define the logic and return the necessary dataset. Let’s start with yet another variation on our  #BookSales<\/code><\/b> temp table:<\/p>\n
    CREATE TABLE #BookSales\r\n(BookType VARCHAR(20), Region VARCHAR(20), \r\n\u00a0 SalesYear INT, BookSales MONEY);\r\nGO\r\n\r\nINSERT INTO #BookSales VALUES('Fiction', 'east', 2014, 11201);\r\nINSERT INTO #BookSales VALUES('Fiction', 'west', 2014, 12939);\r\nINSERT INTO #BookSales VALUES('Fiction', 'west', 2013, 10436);\r\nINSERT INTO #BookSales VALUES('Fiction', 'east', 2013, 9346);\r\nINSERT INTO #BookSales VALUES('Fiction', 'east', 2013, 4356);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 'east', 2014, 7214);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 'west', 2014, 8456);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 'west', 2014, 5800);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 'east', 2013, 8922);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 'west', 2013, 7462);\r\n\t<\/pre>\n
    Suppose we now want to pivot the data based on both the Region<\/code><\/b> and SalesYear<\/code><\/b> columns so that we end up with columns for each year\/region combination. The place to start, of course, is with our table expression:<\/p>\n
    SELECT *\r\nFROM \r\n\u00a0 (SELECT BookType AS BookCategory,\r\n\u00a0\u00a0\u00a0 (CAST(SalesYear AS VARCHAR(4)) + '_' + Region) AS SalesRegion,\r\n\u00a0\u00a0\u00a0 BookSales\r\n\u00a0\u00a0 FROM #BookSales) AS Sales\r\n\u00a0 PIVOT(SUM(BookSales) \r\n\u00a0 FOR SalesRegion \r\n\u00a0\u00a0\u00a0 IN([2013_east], [2013_west], [2014_east], [2014_west])\r\n\u00a0 ) AS PivotSales;\r\n\t<\/pre>\n
    In this case, I’m concatenating the  SalesYear<\/code><\/b> and Region<\/code><\/b> columns to produce the  SalesRegion<\/code><\/b> computed column. This will give me values such as 2014_east<\/code><\/b> and 2014_west<\/code><\/b>. I then specify SalesRegion<\/code><\/b> as the pivot column and pass in the column’s values to the IN<\/code><\/b> operator. The SELECT<\/code><\/b> statement returns the results shown in the following table:<\/p>\n\n\n\n\n\n
    \n
     BookCategory<\/b><\/p>\n<\/td>\n
    		\n
    2013_east<\/b><\/p>\n<\/td>\n
    		\n
    2013_west<\/b><\/p>\n<\/td>\n
    		\n
    2014_east<\/b><\/p>\n<\/td>\n
    		\n
    2014_west<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    13702.00<\/p>\n<\/td>\n
    		\n
    10436.00<\/p>\n<\/td>\n
    		\n
    11201.00<\/p>\n<\/td>\n
    		\n
    12939.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    8922.00<\/p>\n<\/td>\n
    		\n
    7462.00<\/p>\n<\/td>\n
    		\n
    7214.00<\/p>\n<\/td>\n
    		\n
    14256.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As you can see, each book category in the result set is broken down by the year\/region combination. Again, it’s all about the table expression.<\/p>\n
    “I sometimes run queries against a SQL Server 2000 database, which doesn’t support the PIVOT operator. How do I pivot data without using PIVOT<\/code>?”<\/h3>\n
    If you’re working on a SQL Server edition that predates SQL Server 2005 or if you don’t want to use the PIVOT<\/code><\/b> operator for another reason, you can revert to a series of  CASE<\/code><\/b> expressions in the SELECT<\/code><\/b> clause that define each pivoted column. To demonstrate this, we’ll once more create the #BookSales<\/code><\/b> temporary table:<\/p>\n
    CREATE TABLE #BookSales\r\n(BookType VARCHAR(20), SalesYear INT, BookSales MONEY);\r\nGO\r\n\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 11201);\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 12939);\r\nINSERT INTO #BookSales VALUES('Fiction', 2013, 10436);\r\nINSERT INTO #BookSales VALUES('Fiction', 2013, 9346);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, 7214);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, 5800);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 8922);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 7462);\r\n\t<\/pre>\n
    As we’ve seen before, if we were to use the  PIVOT<\/code><\/b> operator, we would end up with a query that looks similar to the following:<\/p>\n
    SELECT *\r\nFROM #BookSales\r\n\u00a0 PIVOT(SUM(BookSales) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n<\/pre>\n
    With results like those shown in the following table:<\/p>\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    19782.00<\/p>\n<\/td>\n
    		\n
    24140.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    16384.00<\/p>\n<\/td>\n
    		\n
    13014.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    We can achieve the same results if we specifically group the data on the BookType<\/code><\/b> column and then use CASE<\/code><\/b> expressions in the SELECT<\/code><\/b> clause:<\/p>\n
    SELECT BookType,\r\n\u00a0 SUM(CASE WHEN SalesYear=2013 THEN BookSales END) AS [2013],\r\n\u00a0 SUM(CASE WHEN SalesYear=2014 THEN BookSales END) AS [2014]\r\nFROM #BookSales\r\nGROUP BY BookType;\r\n\t<\/pre>\n
    The  SELECT<\/code><\/b> clause starts with the BookType<\/code><\/b> column and then includes a  CASE<\/code><\/b> expression for each column we want to include in the pivoted result set. Each CASE<\/code><\/b> expression checks the value in the SalesYear<\/code><\/b> column and returns the related BookSales<\/code><\/b> value for that year.<\/p>\n
    For a simple example like this, the original way of pivoting data isn’t so bad. But if you’re dealing with a lot of unique values in your pivot column, it can become fairly tedious creating all those CASE<\/code><\/b> expressions. On the other hand, it’s nice to have an alternative you can turn to when you don’t want to-or you can’t-use the PIVOT<\/code><\/b> operator.<\/p>\n
    “Does the PIVOT operator let you perform multiple aggregations?”<\/h3>\n
    Yes, you can perform multiple aggregations, but you can’t do it within a single PIVOT<\/code><\/b> clause. To demonstrate, let’s return once again to the  #BookSales<\/code><\/b> table:<\/p>\n
    CREATE TABLE #BookSales\r\n(BookType VARCHAR(20), SalesYear INT, BookSales MONEY);\r\nGO\r\n\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 11201);\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 12939);\r\nINSERT INTO #BookSales VALUES('Fiction', 2013, 10436);\r\nINSERT INTO #BookSales VALUES('Fiction', 2013, 9346);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, 7214);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, 5800);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 8922);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 7462);\r\n\t<\/pre>\n
    Suppose we now want to return both sales totals and averages, in which case, we need to use the SUM<\/code><\/b> and AVG<\/code><\/b> aggregate functions. A fairly straightforward approach to doing this is to create two common table expressions (CTEs) that each pivot the data and then join the two CTEs together, as shown in the following example:<\/p>\n
    WITH\r\nSalesTotal AS\r\n(\r\nSELECT BookType, \r\n\u00a0 [2013] AS [2013_Total],\r\n\u00a0 [2014] AS [2014_Total]\r\nFROM #BookSales\r\n\u00a0 PIVOT(SUM(BookSales) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales\r\n),\r\nSalesAvg AS\r\n(\r\nSELECT BookType, \r\n\u00a0 [2013] AS [2013_Avg],\r\n\u00a0 [2014] AS [2014_Avg]\r\nFROM #BookSales\r\n\u00a0 PIVOT(AVG(BookSales) \r\n\u00a0 FOR SalesYear IN([2013], [2014])\r\n\u00a0 ) AS PivotSales\r\n)\r\nSELECT st.BookType, st.[2013_Total], sa.[2013_Avg],\r\n\u00a0 st.[2014_Total], sa.[2014_Avg]\r\nFROM SalesTotal AS st \r\n\u00a0 INNER JOIN SalesAvg AS sa\r\n\u00a0 ON st.BookType = sa.BookType;\r\n<\/pre>\n
    I first define a CTE named SalesTotal<\/code><\/b> that uses the SUM<\/code><\/b> function to aggregate the data. I then define a CTE named SalesAvg<\/code><\/b> that uses the AVG<\/code><\/b> function to aggregate the data. Notice that in each case the SELECT<\/code><\/b> statement returns pivot column names that are easily identifiable. After I define the two CTEs, I use an inner join to merge them together, based on the BookType<\/code><\/b> column. The statement returns the results shown in the following table:<\/p>\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
     2013_Total<\/b><\/p>\n<\/td>\n
    		\n
    2013_Avg<\/b><\/p>\n<\/td>\n
    		\n
     2014_Total<\/b><\/p>\n<\/td>\n
    		\n
    2014_Avg<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    19782.00<\/p>\n<\/td>\n
    		\n
    9891.00<\/p>\n<\/td>\n
    		\n
    24140.00<\/p>\n<\/td>\n
    		\n
    12070.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    16384.00<\/p>\n<\/td>\n
    		\n
    8192.00<\/p>\n<\/td>\n
    		\n
    13014.00<\/p>\n<\/td>\n
    		\n
    6507.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As you can see, for each book category, we now have a total sales amount as well as an average sales amount for each year. Having the capacity to use CTE’s to join multiple pivot operations helps to make the PIVOT<\/code><\/b> operator much more flexible.<\/p>\n
    “How do I pivot data if I don’t know the values in the pivoted column?”<\/h3>\n
    Up to this point, the examples have assumed that we’ll know the values in our pivoted column at the time we write our query. Often, however, this isn’t the case, which means we have no way of defining the new columns. To overcome this limitation, we need to turn to dynamic SQL to construct our T-SQL statement on the fly. Let’s look at how this works, but first, another incarnation of the  #BookSales<\/code><\/b> table:<\/p>\n
    CREATE TABLE #BookSales\r\n(BookType VARCHAR(20), SalesYear INT, BookSales MONEY);\r\nGO\r\n\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 11201);\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 12939);\r\nINSERT INTO #BookSales VALUES('Fiction', 2013, 10436);\r\nINSERT INTO #BookSales VALUES('Fiction', 2013, 9346);\r\nINSERT INTO #BookSales VALUES('Fiction', 2012, 8956);\r\nINSERT INTO #BookSales VALUES('Fiction', 2012, 7809);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, 7214);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, 5800);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 8922);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 7462);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2012, 7739);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2012, 6842);\r\n\t<\/pre>\n
    Although we’re in on the ground floor in this case, in terms of having created and populated the table, let’s assume we have no idea what the values will be in the  SalesYear<\/code><\/b> column, although we still want to pivot the data based on that column. In this case, we need to construct our query dynamically, pulling the data out of the pivot column as we need it. To do so, we must declare a couple variables, retrieve the list of values from the pivot column, and assemble our query, as shown in the following example:<\/p>\n
    DECLARE @sql AS NVARCHAR(2000);DECLARE @col AS NVARCHAR(2000);\r\n\r\nSELECT @col = ISNULL(@col + ', ', '') + QUOTENAME(SalesYear)\r\nFROM (SELECT DISTINCT SalesYear FROM #BookSales) AS BookTypes;\r\n\r\nSET @sql =\r\n\u00a0 N'SELECT BookType, ' + @col +\r\n\u00a0\u00a0 'FROM #BookSales\r\n\u00a0\u00a0\u00a0 PIVOT(SUM(BookSales)\r\n\u00a0\u00a0\u00a0 FOR SalesYear IN (' + @col + ')) AS PivotSales';\r\n\r\nEXEC sp_executesql @sql;\r\n\t<\/pre>\n
    In this example, I first declare the  @sql<\/code><\/b> and @col<\/code><\/b> variables. The @sql<\/code><\/b> variable will store the final SELECT<\/code><\/b> statement, and the @col<\/code><\/b> variable will store the list of values from the pivot column. Next, I use a SELECT<\/code><\/b> statement to retrieve a list of distinct values from the  SalesYear<\/code><\/b> column and assign those values to the @col<\/code><\/b> variable.<\/p>\n
    Finally, I use a SET<\/code><\/b> statement to construct the final query, passing in the  @col<\/code><\/b> variable to provide the column names to the IN<\/code><\/b> operator and select list, all of which I assign to the  @sql<\/code><\/b> variable. I then use an EXECUTE<\/code><\/b> statement to run the final query, which returns the results shown in the following table:<\/p>\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    2012<\/b><\/p>\n<\/td>\n
    		\n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    16765.00<\/p>\n<\/td>\n
    		\n
    19782.00<\/p>\n<\/td>\n
    		\n
    24140.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    14581.00<\/p>\n<\/td>\n
    		\n
    16384.00<\/p>\n<\/td>\n
    		\n
    13014.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Using dynamic SQL is a great way to get around the need to know the column values in advance, but be sure to take precautions against injection attacks when using dynamic SQL. For example, the QUOTENAME<\/code><\/b> function returns a delimited string to make sure the column name is a valid delimited identifier, helping to avoid injection through object names. Make sure you understand how SQL injection works before using dynamic SQL.<\/p>\n
    “How do I unpivot data in SQL Server?”<\/h3>\n
    You can unpivot data in SQL Server by using the UNPIVOT<\/code><\/b> operator, which in many ways is similar to the  PIVOT<\/code><\/b> operator. As you’ve seen, the PIVOT<\/code><\/b> operator rotates a column’s values, turning them into their own columns. The  UNPIVOT<\/code><\/b> operator does the opposite by rotating columns and turning them into column values. Essentially, the  PIVOT<\/code><\/b> operator denormalizes data, and the UNPIVOT<\/code><\/b> operator attempts to normalize it. For example, suppose our  #BookSales<\/code><\/b> table look more like a denormalized Excel spreadsheet:<\/p>\n
    CREATE TABLE #BookSales\r\n(BookType VARCHAR(20), [2012] MONEY, [2013] MONEY, [2014] MONEY);\r\nGO\r\n\r\nINSERT INTO #BookSales VALUES('Fiction', 16765.00, 19782.00, 24140.00);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 14581.00, 16384.00, 13014.00);\r\n\r\nSELECT * FROM #BookSales;\r\n\t<\/pre>\n
    As expected, the SELECT<\/code><\/b> statement returns the results shown in the following table:<\/p>\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    2012<\/b><\/p>\n<\/td>\n
    		\n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    16765.00<\/p>\n<\/td>\n
    		\n
    19782.00<\/p>\n<\/td>\n
    		\n
    24140.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    14581.00<\/p>\n<\/td>\n
    		\n
    16384.00<\/p>\n<\/td>\n
    		\n
    13014.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Now suppose we want to bring a little more relational-like structure to the data. We can use the UNPIVOT<\/code><\/b> operator to reverse engineer our dataset:<\/p>\n
    SELECT BookType, SalesYear, SalesTotal\r\nFROM #BookSales\r\n\u00a0 UNPIVOT \r\n\u00a0 ( SalesTotal FOR SalesYear IN([2012], [2013], [2014])\r\n\u00a0 ) AS UnpivotSales;\r\n\t<\/pre>\n
    Notice that the FROM<\/code><\/b> clause includes the UNPIVOT<\/code><\/b> subclause, similar to a  PIVOT<\/code><\/b> clause. In the case of  UNPIVOT<\/code><\/b>, however, we first specify a value column (SalesTotal<\/code><\/b>), which will display the sales amounts in our result set, and then include a FOR<\/code><\/b> subclause, which specifies the new pivot column (SalesYear<\/code><\/b>) and the values that will be added to that column. These values are the original column names. The SELECT<\/code><\/b> statement returns the results shown in the following table:<\/p>\n\n\n\n\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    SalesYear<\/b><\/p>\n<\/td>\n
    		\n
     SalesTotal<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2012<\/p>\n<\/td>\n
    		\n
    16765.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2013<\/p>\n<\/td>\n
    		\n
    19782.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2014<\/p>\n<\/td>\n
    		\n
    24140.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    2012<\/p>\n<\/td>\n
    		\n
    14581.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    2013<\/p>\n<\/td>\n
    		\n
    16384.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    2014<\/p>\n<\/td>\n
    		\n
    13014.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As with the PIVOT<\/code><\/b> operator, you want to be sure that the table expression in your  FROM<\/code><\/b> clause returns the right data for the UNPIVOT<\/code><\/b> clause. However, you should not think of UNPIVOT<\/code><\/b> as merely an undoing of a PIVOT<\/code><\/b> operation. For example, suppose that this time around we start with a more normalized version of the #BookSales<\/code><\/b> table:<\/p>\n
    CREATE TABLE #BookSales\r\n(BookType VARCHAR(20), SalesYear INT, BookSales MONEY);\r\nGO\r\n\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 11201);\r\nINSERT INTO #BookSales VALUES('Fiction', 2014, 12939);\r\nINSERT INTO #BookSales VALUES('Fiction', 2013, 10436);\r\nINSERT INTO #BookSales VALUES('Fiction', 2013, 9346);\r\nINSERT INTO #BookSales VALUES('Fiction', 2012, 8956);\r\nINSERT INTO #BookSales VALUES('Fiction', 2012, 7809);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, 7214);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2014, 5800);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 8922);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2013, 7462);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2012, 7739);\r\nINSERT INTO #BookSales VALUES('Nonfiction', 2012, 6842);\r\n\r\nSELECT * FROM #BookSales\r\n<\/pre>\n
    The  SELECT<\/code><\/b> statement returns the results shown in the following table:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    SalesYear<\/b><\/p>\n<\/td>\n
    		\n
    BookSales<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2014<\/p>\n<\/td>\n
    		\n
    11201.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2014<\/p>\n<\/td>\n
    		\n
    12939.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2013<\/p>\n<\/td>\n
    		\n
    10436.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2013<\/p>\n<\/td>\n
    		\n
    9346.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2012<\/p>\n<\/td>\n
    		\n
    8956.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2012<\/p>\n<\/td>\n
    		\n
    7809.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    2014<\/p>\n<\/td>\n
    		\n
    7214.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    2014<\/p>\n<\/td>\n
    		\n
    5800.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    2013<\/p>\n<\/td>\n
    		\n
    8922.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    2013<\/p>\n<\/td>\n
    		\n
    7462.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    2012<\/p>\n<\/td>\n
    		\n
    7739.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    2012<\/p>\n<\/td>\n
    		\n
    6842.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Now let’s pivot the data in the same way we’ve been doing all along:<\/p>\n
    SELECT *\r\nFROM #BookSales\r\n\u00a0 PIVOT(SUM(BookSales) \r\n\u00a0 FOR SalesYear IN([2012], [2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n\r\n<\/pre>\n
    Not surprisingly, the statement returns the following results:<\/p>\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    2012<\/b><\/p>\n<\/td>\n
    		\n
    2013<\/b><\/p>\n<\/td>\n
    		\n
    2014<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    16765.00<\/p>\n<\/td>\n
    		\n
    19782.00<\/p>\n<\/td>\n
    		\n
    24140.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    14581.00<\/p>\n<\/td>\n
    		\n
    16384.00<\/p>\n<\/td>\n
    		\n
    13014.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Instead of just returning the results, however, let’s put them into the #PivotSales<\/code><\/b> temporary table:<\/p>\n
    SELECT *\r\nINTO #PivotSales\r\nFROM #BookSales\r\n\u00a0 PIVOT(SUM(BookSales) \r\n\u00a0 FOR SalesYear IN([2012], [2013], [2014])\r\n\u00a0 ) AS PivotSales;\r\n\r\n<\/pre>\n
    Now let’s unpivot our new temporary table:<\/p>\n
    SELECT BookType, SalesYear, SalesTotal\r\nFROM #PivotSales\r\n\u00a0 UNPIVOT \r\n\u00a0 ( SalesTotal FOR SalesYear IN([2012], [2013], [2014])\r\n\u00a0 ) AS UnpivotSales;\r\n<\/pre>\n
    The  SELECT<\/code><\/b> statement returns the results in the following table:<\/p>\n\n\n\n\n\n\n\n\n\n
    \n
    BookType<\/b><\/p>\n<\/td>\n
    		\n
    SalesYear<\/b><\/p>\n<\/td>\n
    		\n
     SalesTotal<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2012<\/p>\n<\/td>\n
    		\n
    16765.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2013<\/p>\n<\/td>\n
    		\n
    19782.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fiction<\/p>\n<\/td>\n
    		\n
    2014<\/p>\n<\/td>\n
    		\n
    24140.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    2012<\/p>\n<\/td>\n
    		\n
    14581.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    2013<\/p>\n<\/td>\n
    		\n
    16384.00<\/p>\n<\/td>\n<\/tr>\n
    \n
    Nonfiction<\/p>\n<\/td>\n
    		\n
    2014<\/p>\n<\/td>\n
    		\n
    13014.00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    If you compare these to the results we received when we first created the #BookSales<\/code><\/b> table, you’ll see that we don’t get the granularity of the original table. The UNPIVOT<\/code><\/b> operator has no insight into the past and can work only with the dataset in its present form. In general, this shouldn’t present a problem; just don’t expect the  UNPIVOT<\/code><\/b> operator to be able to undo your PIVOT<\/code><\/b> operations.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
    Twelve answered questions about the SQL Server PIVOT operator: how PIVOT rotates data, controlling grouping, column aliases, multi-column pivots, dynamic pivots with unknown values, COUNT(*) workarounds, multiple aggregations, SQL Server 2000 alternatives, and UNPIVOT.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4150,4151,4252,5771],"coauthors":[6779],"class_list":["post-1918","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-sql","tag-sql-server","tag-t-sql-programming","tag-too-shy"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1918","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1918"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1918\/revisions"}],"predecessor-version":[{"id":72681,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1918\/revisions\/72681"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1918"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1918"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1918"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1918"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}