Clash Royale CLAN TAG

Separate values of one column into multiple columns based on other column

I have a table like this with only two columns:

Table: Employees

+----------+-------------+
|Employee | Designation |
+----------+-------------+
| Ron | Manager |
| James | HR |
| Toby | Clerk |
| Amanda | Clerk |
| Jenny | Manager |
| Quentin | HR |
| Roger | Manager |
| Harry | Clerk |
| Sunny | Clerk |
| Rachael | Manager |
+----------+-------------+


I need to write a query to output results like this:

+-----------+--------- +------------+
| Manager | HR | Clerk |
+-----------+----------+------------+
| Jenny | James | Amanda |
| Rachael | Quentin | Harry |
| Roger | null | Sunny |
| Ron | null | Toby |
+-----------+----------+------------+


The employees must be separated into different column based on their designation and ordered alphabetically. There are solutions on the net but most of them either create a new table with an id column with this data in order to solve the problem. Is there a simple solution as an SQL query to solve this without creating any new table?

1 Answer
1

You can use row_number() and some sort of pivoting. I prefer conditional aggregation:

row_number()

select max(case when designation = 'Manager' then employee end) as Manager,
max(case when designation = 'HR' then employee end) as HR,
max(case when designation = 'Clerk' then employee end) as Clerk
from (select e.*,
row_number() over (partition by designation order by employee) as seqnum
from employees e
) e
group by seqnum
order by seqnum;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.