I have the following use-case:

Table Employee has 3 columns- EmpID, OfficeMobile,HomeMobile.

Some employees have given same number for both places. Others have given different. Output should be 2 columns EmpID & ContactNo

If same numbers for both places 1 row else 2 rows.

Expected Output:

I could come up with this query which can compare the two contact numbers but I'm not finding a way to generate two rows when the home and mobile numbers are different.

select ed.empId, 
case 
    when ed.home_number = ed.mobile_number then ed.home_number
    else (ed.home_number )
end as contact
from employeeDetails as ed;

CodePudding user response：

You can use UNION ALL

SELECT ed.empId, ed.home_number as contact
FROM employeeDetails ed

UNION ALL

SELECT ed.empId, ed.mobile_number as contact
FROM employeeDetails ed
WHERE ed.home_number <> ed.mobile_number