Sql-server – PIVOT function Says Invalid Column

pivotsql serversql-server-2012

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?

Best Answer

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 ║
╚══════════╩════╩════╩════╝