Skip to content
Advertisement

SQL pivot the column values

Problem Statement:

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

Note: Print NULL when there are no more names corresponding to an occupation.

Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.

Sample Input

enter image description here

Sample Output


My solution:

Error:

ERROR 1064 (42000) at line 1: 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 ” at line 7

Line no 7:

I am trying to learn Advanced sql concepts so as far as now I am able to write the query but I am getting this error. When I run the subquery it works fine and I get some output but after using pivot I get this error. I don’t know how to solve it and what’s causing this error.

I want to do it by pivot operator and without using row_number.

Subquery:

Subquery output:

Advertisement

Answer

You can use window functions and conditional aggregation:

The subquery ranks persons having the same occupatin by name. You can then use that information to generate the rows, and access the corresponding name for each occupation with a conditional aggregate.

Without window functions, it is different. If your data is not too large, one option emulates row number with a subquery:

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement