I'm trying to teach myself to use the SQL Server 2012 PIVOT function, using the old example pubs database from Microsoft, I have written this code..
SELECT [au_lname], [CA], [OR], [IN] FROM (SELECT [au_lname], [state], [au_id] FROM [authors] WHERE STATE IN('CA','OR','IN')) AS Data PIVOT (COUNT([au_id]) FOR au_lname IN([CA], [OR], [IN])) as PvtTbl;
[au_lname] has a red wavy line under it, and when I try and execute the code, I get the error message:
"Msg 207, Level 16, State 1, Line 1
Invalid column name 'au_lname'."
I've tried refreshing the IntelliSence Local Cache (Ctrl + Shift + R) but this didn't help. The column definitely exists in the [authors] table, and this code returns results as expected:
SELECT [au_lname] FROM [authors]
Please can someone tell me if there an error in my
PIVOT function code, or is there something else going wrong?