I have one practice question of SQL,
In which we have to pivot the given table by occupation
output should be
Doctor Professor Singer Actor
Jenny Ashley Meera Jane
Samantha Christeen Priya Julia
NULL Ketty NULL Maria
how could i do this ?
Here is the Solution to the above Question using CASE-WHEN Statement and Row_Number Windows Function to Pivot the above data.
With Result as
(
SELECT *, ROW_NUMBER () OVER (PARTITION BY Occupation ORDER BY name) AS rn
FROM employee
)
SELECT
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 Result
GROUP BY rn
ORDER BY Doctor, Professor,Singer, Actor ;
I didn’t understand the solution, why MAX is used?
can you elaborate it?
it is working with MIN without order by also
With Result as
(SELECT *, ROW_NUMBER () OVER (PARTITION BY Occupation ORDER BY name) AS rn
FROM occupations)
Select
MIN(CASE WHEN Occupation = 'Doctor' THEN Name END) as Doctor,
MIN(CASE WHEN Occupation = 'Professor' THEN Name END) as Professor,
MIN(CASE WHEN Occupation = 'Singer' THEN Name END) as Singer,
MIN(CASE WHEN Occupation = 'Actor' THEN Name END) as Actor
From result
group by rn
using MAX with orderby or just using MIN are two different ways of solving the same problem. The only difference is MAX uses one more step so may be MIN is better. But the main point is the above code solves the issues of pivoting.
Do you have any problem in understanding how pivoting is happening because of above codes?
YES…dont understand how pivoting is happened?
Hi ,
One way to solve this using CASE statements , You can also use crosstab function in postgreSQL , go through the following link for better understanding
sql - PostgreSQL Crosstab Query - Stack Overflow.
The crosstab function only works with PostgreSQL and Pivot and unpivot function work with MySQL and MSSQL Server, DB2 also.