Title: MySQL query to show salary depending on department
Tags: mysql;sql
Question: I am stuck with a peculiar problem. I have tried almost everything that I know.
The table contains the following details:
+--------+---------+--------+----------+
| emp_id | dept_no | salary | emp_name |
+--------+---------+--------+----------+
|      1 | c1      |  12300 | Rajesh   |
|      2 | c2      |  45123 | Arvind   |
|      3 | c3      |  12345 | Ravi     |
+--------+---------+--------+----------+
The expected output is like this :
+---------+--------+--------+--------+
| Dept_no | DeptC1 | Deptc2 | DeptC3 |
+---------+--------+--------+--------+
| c1      |  12300 |      0 |      0 |
| c2      |      0 |  45123 |      0 |
| c3      |      0 |      0 |  12345 |
+---------+--------+--------+--------+
I am really stuck with this problem and have no idea how to solve this. I was trying to solve this with left outer joins but was not able to do it.
Comment: that is so ineffective, because if you have 100 depts, then you have to make 100 columns too. isn't it?

Comment: This is the most ridiculous, pointless expected output I have ever seen

Comment: why do you need this kind of output?

Comment: It might seem pointless but this is what's required. Can this be done? if yes then how? please suggest

Comment: @bprayudha yes you are right. In my scenario there was a fixed number of depts. only 5

Here is the accepted answer: Try this
SELECT Dept_no,
if(Dept_no='C1',salary,0) as Dept_noC1,
if(Dept_no='C2',salary,0) as Dept_noC2,
if(Dept_no='C3',salary,0) as Dept_noC3
From Emp
Group by Dept_no;
Fiddle Demo
Output:
+---------+--------+--------+--------+
| Dept_no | DeptC1 | Deptc2 | DeptC3 |
+---------+--------+--------+--------+
| c1      |  12300 |      0 |      0 |
| c2      |      0 |  45123 |      0 |
| c3      |      0 |      0 |  12345 |
+---------+--------+--------+--------+
Comment for this answer: I think for that you have to go for dynamic queries
Comment for this answer: Thanks this works. Is there any query that will work where we dont have a fixed number of department_no?


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 5, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:03/01/14', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<mysql><sql>', 'Title': 'MySQL query to show salary depending on department', 'ViewCount': 51, 'id': 22110053, 'user_AboutMe': '<p>I am MCA (Master of Computer Application) student. I love to code in java and try out all sorts of new programs and test the limitations of my ability. I also love to learn new things which I might have missed out. </p>\n\n<p>" Nothing is Impossible" is what I believe in.</p>\n', 'user_AccountId': 1818283.0, 'user_DisplayName': 'Biman Ghosh', 'user_Reputation': 33.0, 'user_Views': 23.0, 'user_id': 1704345.0}}