CODEWITHSUNDEEP
phpmysqldatabasemysqli
basagabi
basagabi

Reputation: 5064

MySQL: Group ids and display different values on a separate column

How to display employee with different phone numbers in one row only wherein phone numbers are displayed by column?

Create Table Employees(
  Employee_ID INT,
  PhoneNumber VARCHAR(50)
  );

INSERT INTO Employees VALUES (1,'111'),(1,'222'),(2,'111'),(2,'222'),(3,'111'),(3,'222'),(4,'111'),(5,'111'),(5,'222')

Here's what I've tried but it seems no to work.

SELECT *
FROM Employees
WHERE PhoneNumber IN (
    SELECT PhoneNumber
    FROM Employees
    GROUP BY PhoneNumber
    HAVING COUNT(Employee_ID) > 1
    )

You may check it out on this SQL Fiddle.

Output should be:

Employee_ID | PhoneNumber_1 | PhoneNumber_2
1           | 111           | 222
2           | 111           | 222
3           | 111           | 222
4           | 111           | null
5           | 111           | 222

Assuming that every employee will only have a max of two phone numbers.

Is this even possible using mysql only to display this kind of result? Another way is to just get employee_ids the group them then make another query to get their phonenumbers but that will make 2 queries. The goal here is to achieve this output using only 1 query statement.

Upvotes: 2

Views: 132

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

You can use aggregation pivot the data.

SELECT 
    Employee_ID, 
    min(PhoneNumber) as Phone1,
    case when min(PhoneNumber) <> max(PhoneNumber) then max(PhoneNumber) end as Phone2
FROM Employees
group by Employee_ID;

The Case expression is used to check if there is more than one distinct phone number, then only display the second number or else display null. You can use count also if you want to check only count (not distinct phone numbers).

SELECT 
    Employee_ID, 
    min(PhoneNumber) as Phone1,
    case when count(*) > 1 then max(PhoneNumber) end as Phone2
FROM Employees
group by Employee_ID;

Upvotes: 2

Related Questions