2

How do I name the columns of a PIVOT table to something that is not a number?

Is it a requirement that the names of the spreading columns be a number?

I'm working my way thought the book Querying Microsoft SQL Server 2012 in preparation for the 70-461 exam. I am currently on the lesson for PIVOT tables (Chapter 5, Lesson 2, Page 163). The following SQL executes successfully.

WITH Pivot234
AS
(
select  ss.companyname,                         --Grouping column
        datepart(dw, so.orderdate) as DayNumber, --Spreading column
        so.freight                              --Aggregation Column
FROM Sales.Orders so
LEFT OUTER JOIN sales.Shippers ss ON so.shipperid = ss.shipperid 
) 

SELECT  companyname, [1],[2],[3],[4],[5],[6],[7]
FROM Pivot234
PIVOT (AVG(FREIGHT) 
       FOR DayNumber IN ([1],[2],[3],[4],[5],[6],[7])
       ) AS p;

I want the name of my attributes to be the days of the week not numbers. When I change the numbers in the PIVOT table operator to the days of the week I get the following...

WITH Pivot234
AS
(
select  ss.companyname,                         --Grouping column
        datepart(dw, so.orderdate) as DayNumber, --Spreading column
        so.freight                              --Aggregation Column
FROM Sales.Orders so
LEFT OUTER JOIN sales.Shippers ss ON so.shipperid = ss.shipperid 
) 

SELECT  companyname, [Mon],[Tues],[Wed],[Thurs],[Fri],[Sat],[Sun]
FROM Pivot234
PIVOT (AVG(FREIGHT) 
       FOR DayNumber IN ([Mon],[Tues],[Wed],[Thurs],[Fri],[Sat],[Sun])
       ) AS p;

The second SQL query has the following error...

Msg 8114, Level 16, State 1, Line 36 Error converting data type nvarchar to int. Msg 473, Level 16, State 1, Line 36 The incorrect value "Mon" is supplied in the PIVOT operator.

Line 36 is FOR DayNumber IN ([Mon],[Tues],[Wed],[Thurs],[Fri],[Sat],[Sun])

What am I not understanding about PIVOT tables?

3

Alias the columns in the select. The values in the In (...) part of the Pivot need to be the exact field values observed in the data.

Select CompanyName, [1] As Mon, [2] As Tues, [3] As Wed...
  From Pivot234
  Pivot (Avg(Freight) For DayNumber In ([1], [2], [3], [4], [5], [6], [7])) As p;

Can you explain why it is necessary to alias the spreading columns? Why can I not just simply state the columns as I did in the PIVOT table operator and in the SELECT statement?

The Pivot(Sum([ValueField]) For [CategoryField] In ('Category A', 'Category B' ... )) syntax is looking for the literal values that you supply in the In () to be in the [CategoryField]. It isn't mapping number 1 to position 1, it is searching the field for those exact values.

1

PIVOT works fine on strings as well as integers.

The PIVOT operator has to work with the values that are provided to it. In your example the driving query uses the DATEPART function to populate values into a column you name DayNumber. This function returns an integer. Therefore the PIVOT has to reference integer values.

There are two paths to achieve the desired result - either alias the output of the PIVOT, or provide appropriately named input. There are many ways to achieve the latter. One would be to use the DATENAME function instead of DATEPART. Another would be to use a CASE to map from number to string. A third would be to hold the mapping in a table.

-6

Both DayNumber and Freight are required to be of the same data type

  • 1
    No they are not... Where on earth did you get that from? – Mark Sinkinson Jun 22 '17 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.