Buckets as column in MySQL

Meghna 0
Viewed 15

Q: The column should be created based on the Occupation like Doctor, Teacher, Sweeper, Lawyer which will contain the names ordered alphabetically.

The table:

Name  | Occupation
------------------
Amex  |   Teacher
Joe   |   Lawyer
Colin |   Doctor
Fenny |  Sweeper
Lio   |  Doctor
James |  Teacher
Kevin |  Teacher
Megh  |  Lawyer

Output:

Colin  Amex Fenny Joe
Lio    James NULL Megh
NULL   Kevin NULL NULL

I have tried doing pivoting, bucketing and partitioning yet it's not working.

mysql
1 Answers

Static occupation list pivotting:

WITH cte AS (
  SELECT *, ROW_NUMBER()  OVER (PARTITION BY Occupation ORDER BY Name) rn
  FROM test
)
SELECT MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor,
       MAX(CASE WHEN Occupation = 'Teacher' THEN Name END) AS Teacher,
       MAX(CASE WHEN Occupation = 'Sweeper' THEN Name END) AS Sweeper,
       MAX(CASE WHEN Occupation = 'Lawyer' THEN Name END) AS Lawyer
FROM cte
GROUP BY rn
ORDER BY rn;
Doctor Teacher Sweeper Lawyer
Colin Amex Fenny Joe
Lio James null Megh
null Kevin null null

fiddle

Akina0
Related