Write View to convert table with two columns to have multiple columns

Question

How can I write a SQL query for TABLE 1 to display a VIEW and get a output like the second table.

TABLE 1
+-------------+---------------+
|EMPLOYER ID  | EMPLOYEE NAME |
+-------------+---------------+
|1            |JOHN           |
+-------------+---------------+
|1            |MICHAEL        |
+-------------+---------------+
|1            |FRANK          |
+-------------+---------------+
|2            |JOHN           |
+-------------+---------------+
|2            |JACK           |
+-------------+---------------+

I want to write a view to display a table like this?

+-------------+----------------+----------------+---------------+
|EMPLOYER ID  | EMPLOYEE NAME 1| EMPLOYEE NAME 2|EMPLOYEE NAME 3|
+-------------+----------------+----------------+---------------+
|1            | JOHN           | MICHAEL        | FRANK         |
+-------------+----------------+----------------+---------------+
|2            | JOHN           | JACK           | NULL          |
+-------------+----------------+----------------+---------------+

Does this answer your question? https://stackoverflow.com/questions/1241178/mysql-rows-to-columns — Kraigolas, Feb 27 '21 at 00:59

score 2 · Accepted Answer · answered Feb 27 '21 at 01:00

You can use conditional aggregation:

select employer_id,
       max(case when seqnum = 1 then employee_name end) as employee_name_1,
       max(case when seqnum = 2 then employee_name end) as employee_name_2,
       max(case when seqnum = 3 then employee_name end) as employee_name_3
from (select t1.*,
             row_number() over (partition by employer_id order by employee_name) as seqnum
      from table1 t1
     ) t1
group by employer_id;

This works for three names (and can obviously be extended to more). However, if you don't don't the number of names, it might be more convenient to combine them into a single string:

select employer_id,
       group_concat(employee_name) as employee_names
from table1
group by employer_id;

I don't fully understand what its doing it but it worked, so thank you very much. — Serbian Guru, Feb 27 '21 at 01:27

Write View to convert table with two columns to have multiple columns

1 Answers1