PIVOT function Says Invalid Column

Question

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;

The column [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?

Answer 1

You can't select the column you're pivoting on.

Change your query to SELECT PvtTbl.*, like this:

SELECT PvtTbl.*

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;

The other columns from the source table, authors, are automatically added to the columns returned by the PIVOT clause, so output contains the state column, as well as [CA], [OR], [IN].

I'm attempting to infer your desired output. It looks like you want a count of each au_lname for each state. The most likely "correct" pivot would be:

USE tempdb;
IF OBJECT_ID(N'dbo.authors', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.authors;
END
GO
CREATE TABLE dbo.authors
(
    au_lname sysname
    , [state] sysname
    , [au_id] int
);

INSERT INTO dbo.authors (au_lname, [state], au_id)
VALUES ('Vernon', 'CA', 1)
    , ('Darling', 'OR', 2)
    , ('Obbish', 'IN', 3)
    , ('White', 'CA', 4)
    , ('Vernon', 'OR', 5)
    , ('Vernon', 'OR', 6);

SELECT  *

FROM    (
        SELECT [au_lname], [state], [au_id]
        FROM [authors]
        WHERE STATE IN('CA','OR','IN')
        ) AS Data

PIVOT   (
    COUNT([au_id]) 
    FOR [state] IN(
        [CA], [OR], [IN])
    ) as PvtTbl;

The output looks like:

╔══════════╦════╦════╦════╗
║ au_lname ║ CA ║ OR ║ IN ║
╠══════════╬════╬════╬════╣
║ Darling  ║  0 ║  1 ║  0 ║
║ Obbish   ║  0 ║  0 ║  1 ║
║ Vernon   ║  1 ║  2 ║  0 ║
║ White    ║  1 ║  0 ║  0 ║
╚══════════╩════╩════╩════╝