SQL - error when using pivot command

Question

I am having trouble using the pivot command - I am getting the following error

"Invalid column name '2013-03-22'. Invalid column name '2013-03-29'. Invalid column name '2013-04-05'. Invalid column name 'Volume'. Invalid column name 'Week Ending Date'."

when running this code

SELECT * 
FROM
(
   SELECT [Area], 
     [Region], 
     [Channel], 
     [Controller], 
     [2013-03-22] AS _dt1, 
     [2013-03-29] AS _dt2, 
     [2013-04-05] AS _dt3 
  FROM [DataTable]
) AS SourceTable
PIVOT
(
   SUM ([Volume]) 
   FOR [Week Ending Date] IN ([2013-03-22], [2013-03-29], [2013-04-05])
) AS PivotTable

It seems to fit the correct format.. any ideas?

You've renamed your columns to `_dt1`, `_dt2`, etc in your subquery so the columns `[2013-03-22], [2013-03-29], [2013-04-05]` don't exist. You need to use the names of the aliases or skip the alias. Plus you have no column named `Volume`, or `Week Ending Date`. — Taryn, Dec 29 '15 at 20:34
@bluefeet: thanks, but isn't the idea to get rid of the volume and week columns through pivoting? — dashnick, Dec 29 '15 at 20:44
Yes, it looks like you want to sum it, but you don't have a `volume` column in your subquery. If the column doesn't exist in the subquery it can't sum it. What column in your subquery contains the volume value? Can you create a SQL Fiddle with some sample data? Or even edit your question to include sample data? — Taryn, Dec 29 '15 at 20:45
thanks @bluefeet.. I can try this, but it seems more like a compilation error than a data error.. if I include [Volume] and [Week Ending Date] in the subquery, those errors go away, but I still get the error for the date columns... — dashnick, Dec 29 '15 at 21:04
Without seeing sample data and the final desired result, I'm guessing here but typically you pivot values that are in a single column - the subquery has your dates in separate columns which will prevent the pivot from working. What exactly are you trying to do with the query? I've answered a lot of pivot questions and it's hard to see what you want as a final result without data. You might want to look at [this](http://stackoverflow.com/a/15745076/426671) or [this](http://stackoverflow.com/a/19591362/426671) answer to help figure out the syntax. — Taryn, Dec 29 '15 at 21:07
thanks @bluefeet - I seem to have figured it out, see my response below. Greatly appreciate the help! — dashnick, Dec 29 '15 at 21:58

dashnick · Accepted Answer · 2015-12-30T17:29:58.813

Seems that source table should not include the values for the columns to be pivoted, but should contain the columns containing the values to be pivoted and the values to be pivoted. See below:

SELECT [Area], 
[Region], 
[Channel], 
[Controller],
[2013-03-22] as _dt1, 
[2013-03-29] as _dt2, 
[2013-04-05] as _dt3
FROM 
(
SELECT [Area], 
[Region], 
[Channel], 
[Controller], 
[Volume],
[Week Ending Date]
FROM [DataTable]
) AS SourceTable 
PIVOT(SUM ([Volume]) FOR [Week Ending Date] IN ([2013-03-22], [2013-03-29], 
[2013-04-05])) 
AS PivotTable

Valentin Petkov · Answer 2 · 2015-12-30T15:29:00.393

usually I do my pivot columns dynamically then I run execute sp_executesql @query

find the distinct DATEs '2013-03-22' ... then turn it to string use STUFF and XML PATH
replace it in your embedded SQL query

so you never HARD CODE YOUR COLUMNS

here example I have

 DECLARE @cols AS NVARCHAR(MAX),   @query  AS NVARCHAR(MAX)
   select @cols = STUFF((SELECT  N',' + QUOTENAME(c.name) 
   FROM sys.tables AS t
   INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
   WHERE t.name = 'tagCloudLibrary'
    and c.name not in ('langID')
    FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)')  ,1,1,'')


    set @query = N'SELECT ' + @cols + N' from tagCloudLibrary'
    execute sp_executesql @query;

HERE if your PIVOT is correct do CORRECT WHERE CLAUSE for STUFF...

  DECLARE @cols AS NVARCHAR(MAX),   @query  AS NVARCHAR(MAX)
   select @cols = STUFF((SELECT top 10  N',' + QUOTENAME(LastActivityDate)  
   FROM DataTable   
     FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)')  ,1,1,'')

     select @cols

     set query = N'SELECT * FROM
      (SELECT [Area], [Region], [Channel], [Controller], ' +  @cols 
     + N' FROM [DataTable]) AS SourceTable '
    + N' PIVOT  (SUM ([Volume]) FOR [Week Ending Date] IN (' +  @cols  + ') AS PivotTable '

     execute sp_executesql @query;

Obviously who downgrade my solution never in his life did programmatically PIVOTTING and HARD CODE HIS COLUMNS — Valentin Petkov, Dec 29 '15 at 21:01

SQL - error when using pivot command

2 Answers2