CODEWITHSUNDEEP
sqlsql-serverpivot
Matt
Matt

Reputation: 1632

Pivot Table Incorrect Syntax

Im new to SQL and trying to make my first pivot table. The code below yields the error syntax error at or near "PIVOT". Any help would be much appreciated! I'm definitely making some basic syntax error but I cant find it for the life of me.

SELECT p.SO, p.FR, p.SR, p.JR
FROM
  (SELECT players.year AS year
    FROM benn.college_football_players players) sub

PIVOT
(
  COUNT(sub.year) FOR sub.year IN ([SO], [FR], [SR], [JR])
)
AS p

Below is sample data returned from benn.college_football_players when the subquery is run on its own

year
JR
SO
SO
FR
SR
JR

Upvotes: 0

Views: 130

Answers (2)

D-Shih
D-Shih

Reputation: 46229

if your doesn't accept the PIVOT command. I would use conditional aggregate function use CASE WHEN with COUNT instead of it.

For example, mysql didn't support PIVOT command, but you can use aggregate function do PIVOT

CREATE TABLE college_football_players(
   Year varchar(50)
);


insert into college_football_players values ('JR');
insert into college_football_players values ('SO');
insert into college_football_players values ('SO');
insert into college_football_players values ('FR');
insert into college_football_players values ('SR');
insert into college_football_players values ('JR');

Query 1:

SELECT COUNT(CASE WHEN year = 'SO' then 1 end) SO,
       COUNT(CASE WHEN year = 'FR' then 1 end) FR,
       COUNT(CASE WHEN year = 'SR' then 1 end) SR,
       COUNT(CASE WHEN year = 'JR' then 1 end) JR
FROM  benn.college_football_players players players

Results:

| SO | FR | SR | JR |
|----|----|----|----|
|  2 |  1 |  1 |  2 |

Upvotes: 2

Matt
Matt

Reputation: 1632

The issue wasn't the code but that I was running the code on the MODE Analytics SQL tutorial website. The website doesn't seem to accept the PIVOT command.

Upvotes: 0

Related Questions