[SOLVED] Are there limitations in a SELECT statement when writing a pivot query

Are there limitations in a SELECT statement when writing a pivot query

I'm trying to write a pivot query against a SQL Server database, and SQLQuery is complaining about the syntax (background: I have a working pivot query that uses a different table, and I'm trying to make a version for a new table).

I've been reading articles about pivot queries and searching for limitations on the SELECT portion, and I haven't found an explanation yet. The error message I get is that I'm using invalid column names, but if I just run a SELECT query without the pivot portion, then it works.

I have a table Centers with these columns:

Centers_RiskID (varchar(21), not null)
Centers_Change_Number (smallint, not null)
Centers_Center_Code (varchar(5), not null)
Centers_Change_Indicator (bit, not null)

The query that fails is:

SELECT
    Centers_RiskID, Centers_Change_Number, Centers_Center_Code
FROM
    Centers
PIVOT
    (MAX(Centers_Change_Number) 
     FOR [Centers_Center_Code] IN ([APP], [HQ], [MSFT])) AS pivot_table1

Centers_Change_Number and Centers_Center_Code on the first line are flagged as illegal column names.

But if I take off the pivot statement, then this query works.

SELECT 
    Centers_RiskID, Centers_Change_Number, Centers_Center_Code
FROM
    Centers

I'm using the SQLQuery app to test the queries.

Solution

From what I understand the SELECT statement should reference the columns after the pivoting operation has been applied

SELECT Centers_RiskID, [APP], [HQ], [MSFT]
FROM (
  SELECT Centers_RiskID, Centers_Center_Code, Centers_Change_Number
  FROM Centers
) AS SourceTable
PIVOT (
  MAX(Centers_Change_Number)
  FOR Centers_Center_Code IN ([APP], [HQ], [MSFT])
) AS pivot_table1;

This is a helpful resource Link