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
– Taryn, Commented Dec 29, 2015 at 20:34
@bluefeet: thanks, but isn't the idea to get rid of the volume and week columns through pivoting? — dashnick
– dashnick, Commented Dec 29, 2015 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
– Taryn, Commented Dec 29, 2015 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
– dashnick, Commented Dec 29, 2015 at 21:04
thanks @bluefeet - I seem to have figured it out, see my response below. Greatly appreciate the help! — dashnick
– dashnick, Commented Dec 29, 2015 at 21:58

dashnick · Accepted Answer · 2015-12-30 17:29:58Z

2

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

edited Dec 30, 2015 at 17:29

answered Dec 29, 2015 at 21:58

dashnick

2,0781 gold badge22 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Valentin Petkov · Accepted Answer · 2015-12-30 15:29:00Z

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

Collectives™ on Stack Overflow

SQL - error when using pivot command

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related