1

I have an employee table like below:

Emp_id Name Salary
1      Abc   10
2      Acd   90
3      Bcd   40

And, a degrees table like below:

Degree_Id Degree_name College_Id College_name Emp_Id
1            UG           1          MIT          2
2            PG           3          Harvard      2
1            UG           1          MIT          1
2            PG           3          Harvard      1

Above table stores various degrees acquired by the employee.

I need output like below:

Emp_Id  College_1  College_2
2        MIT        Harvard
1        MIT        Harvard

What should be my query for this? My database is MySql.

Mayank Porwal
  • 33,470
  • 8
  • 37
  • 58
  • How many colleges exist? Is there a possibility that someone has been in more than two colleges? which colleges should be in `College_1` column and which in `College_2`? – xpy Feb 04 '16 at 09:38
  • @xpy.. An employee could have acquired any number of degrees from any number of colleges. All that data would be stored in the Degrees table. I need to find out a list which shows number of colleges attended by every employee. So, columns College_1 and College_2 can increase depending on the employee. – Mayank Porwal Feb 04 '16 at 09:45
  • Then you will need to `pivot` the table, see here: http://stackoverflow.com/questions/7674786/mysql-pivot-table – xpy Feb 04 '16 at 09:47

1 Answers1

0

Here is one way to realize a similar effect, but not exactly the same as your expectation.

SELECT
Emp_id,
GROUP_CONCAT(College_name ORDER BY College_name DESC SEPARATOR '  ') Colleges
FROM degree
GROUP BY Emp_id
ORDER BY Emp_id DESC;

mysql> SELECT
    -> Emp_id,
    -> GROUP_CONCAT(College_name ORDER BY College_name DESC SEPARATOR '  ') Colleges
    -> FROM degree
    -> GROUP BY Emp_id
    -> ORDER BY Emp_id DESC;
+--------+--------------+
| Emp_id | Colleges     |
+--------+--------------+
|      2 | MIT  Harvard |
|      1 | MIT  Harvard |
+--------+--------------+
2 rows in set (0.00 sec)
Dylan Su
  • 5,975
  • 1
  • 16
  • 25