CODEWITHSUNDEEP
mysqlsql
Oyedele Femi
Oyedele Femi

Reputation: 167

Error Code while using Pivot in SQL

Presently solving a challenge question and i have been trying to solve the issue i am facing but unable to do so. Your expertise would be highly appreciated.

Here is the question:

Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should Doctor, Professor, Singer, and Actor, respectively.

My Solution

SELECT
    [Doctor],
    [Professor],
    [Singer],
    [Actor]
FROM
    (SELECT 
         ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) ma,
         [Name],
         [Occupations] 
     FROM 
         Occupation
    ) AS source 
PIVOT
    ( MAX([Name]) FOR [occupation] IN ([Doctor],[Professor],[Singer],[Actor]) ) as pv
ORDER BY ma

Error Message

ERROR 1064 (42000) at line 5: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Doctor],
    [Professor],
    [Singer],
    [Actor]
FROM
    (SELECT 
         ' at line 2

Awaiting your replies. Thanks

Upvotes: 1

Views: 4085

Answers (2)

Kashif Mansoorie
Kashif Mansoorie

Reputation: 1

For Mysql , you can solve it this way:

set @d=0, @p=0, @s=0, @a=0;

select min(Doctor), min(Professor), min(Singer), min(Actor)
from(
  select case 
            when Occupation='Doctor' then (@d:=@d+1)
            when Occupation='Professor' then (@p:=@p+1)
            when Occupation='Singer' then (@s:=@s+1)
            when Occupation='Actor' then (@a:=@a+1) 
            end as Rowline,
        case when Occupation='Doctor' then Name end as Doctor,
        case when Occupation='Professor' then Name end as Professor,
        case when Occupation='Singer' then Name end as Singer,
        case when Occupation='Actor' then Name end as Actor
  from OCCUPATIONS
  order by Name
) as temp
group by Rowline;

Hope this will resolve your issue.

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93764

Mysql does not support PIVOT operator

use conditional aggregate to pivot the data.

Since the names are split into 4 different columns, Ordering by name after pivoting the data does not make lot of sense. 

select 
Occupations,
max(case when occupation = 'Doctor' then name end) as Doctor,
max(case when occupation = 'Professor' then name end) as Professor,
max(case when occupation = 'Singer' then name end) as Singer,
max(case when occupation = 'Actor' then name end) as Actor
from Occupation
Group by Occupations

Also instead of square brackets [] you should use backticks ``

Upvotes: 4

Related Questions