3

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?

0

1 Answer 1

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.