Sql-server – PIVOT data type conversion error

pivotsql server

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?

Best Answer

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.

Related Solutions

Sql-server – Find Iteration of Day of Week in Month

It's actually surprisingly easy using DAY(), as 1-7 will be 1, 8-14 will be 2, 15-21 will be 3, 22-28 will be 4 and 29-31 will be 5. Doesn't matter what the name of the day is, that will always hold true.

select case when DAY(yourdate) <=7  then 1
            when DAY(yourdate) <=14 then 2
            when DAY(yourdate) <=21 then 3
            when DAY(yourdate) <=28 then 4
            else 5
        end as occurance
from yourtable;

(case short-circuits)

Sql-server – Dynamic conversion of long data to wide with multiple columns

are you looking for this,

CREATE TABLE #T(ID INT,SchoolID INT,Section INT,RepScore DECIMAL(5,2)
,SportsScore DECIMAL(5,2),PartyScore DECIMAL(5,2))
INSERT INTO #T VALUES
(1,20,1 ,23.2,70.2,42.3)         
,(2,20,6 ,65.2,75.8,52.3)       
,(3,20,7 ,77.2,72.2,66.3)      
,(4,21,10,13.2,40.2,72.3)        
,(5,21,11,25.2,55.8,72.3)        
,(6,21,12,37.2,62.2,76.3) 
--SELECT * FROM #T

DECLARE @colRpName VARCHAR(1000)
DECLARE @colRpVal VARCHAR(1000)
DECLARE @colSportVal VARCHAR(1000)
DECLARE @colPartyVal VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)

SELECT  
@colRpName = STUFF(( SELECT distinct  '], [' + cast([Section] as varchar)
                        FROM #T t1  FOR XML PATH('') ), 1, 2, '') + ']'
,  @colRpVal = STUFF(( SELECT distinct  ', [' + cast([Section] as varchar) +'] as '+'[a ' + cast([Section] as varchar)+']'
                        FROM #T t1  FOR XML PATH('') ), 1, 2, '') + ''
,  @colSportVal = STUFF(( SELECT distinct  ', [' + cast([Section] as varchar) +'] as '+'[b ' + cast([Section] as varchar)+']'
                        FROM #T t1  FOR XML PATH('') ), 1, 2, '') + ''
,  @colPartyVal = STUFF(( SELECT distinct  ', [' + cast([Section] as varchar) +'] as '+'[c ' + cast([Section] as varchar)+']'
                        FROM #T t1 FOR XML PATH('') ), 1, 2, '') + ''
from #T t

--SELECT @colRpName,@colRpVal

SET @sqlquery = '
SELECT fp.SchoolID,'+@colRpVal+'
,'+@colSportVal+'
,'+@colPartyVal+'
FROM  
(SELECT SchoolID,[section],RepScore

FROM #T z) base  
PIVOT (sum(RepScore) FOR [section] IN ('+@colRpName+')) AS   fp
CROSS APPLY 
(
SELECT fp1.SchoolID,'+@colSportVal+'
FROM  
(SELECT SchoolID,[section],SportsScore

FROM #T z) base1  
PIVOT (sum(SportsScore) FOR [section] IN ('+@colRpName+')) AS   fp1
WHERE fp1.SchoolID=fp.SchoolID
)ca
CROSS APPLY 
(
SELECT fp2.SchoolID,'+@colPartyVal+'
FROM  
(SELECT SchoolID,[section],PartyScore

FROM #T z) base2
PIVOT (sum(PartyScore) FOR [section] IN ('+@colRpName+')) AS   fp2
WHERE fp2.SchoolID=fp.SchoolID
)ca1
  ' 

--print @sqlquery
  EXEC(@sqlquery)-- please use sp_executesql

DROP TABLE #T

Best Answer

Related Solutions

Sql-server – Find Iteration of Day of Week in Month

Sql-server – Dynamic conversion of long data to wide with multiple columns

Related Question