sql help required

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Sid
Fan

sql help required

CREATE TABLE TEST
(
ID VARCHAR(2),
PHN VARCHAR(10),
MOB VARCHAR(10)
);

INSERT INTO TEST VALUES ('A','111','111');
INSERT INTO TEST VALUES ('B','222','222');
INSERT INTO TEST VALUES ('C','333','444');

 

SELECT * FROM TEST; 

ID PHN MOB

A111111
B222222
C333444

 

I want ot write a query to display ID and Contact such that when PHN and MOB are not same the display both in separate rows else display only 1 record. Below is the required output.

IDContact
A111
B222
C333
C444

Accepted Solutions
Junior Contributor

Re: sql help required

Your case is not needed, could be simplified to 

 

SELECT ID,PHN AS CONTACT
FROM TEST
UNION
SELECT ID,MOB  AS CONTACT
FROM TEST
;

But both queries apply UNION DISTINCT which results in two redistribution steps plus a final sort eliminating duplicate rows.

 

Better move the CASE into WHERE and then you can switch to UNION ALL:

SELECT ID,PHN AS CONTACT
FROM TEST
UNION ALL
SELECT ID,MOB  AS CONTACT
WHERE PHN<>MOB
FROM TEST;

 

1 ACCEPTED SOLUTION
3 REPLIES
Sid
Fan

Re: sql help required

I have got 1 solution to get this.

 

SELECT ID,PHN AS CONTACT
FROM TEST
UNION
SELECT ID,CASE WHEN PHN=MOB THEN PHN ELSE MOB END AS CONTACT
FROM TEST;

 

Really appreciate if there are any other possible solutions.

Junior Contributor

Re: sql help required

Your case is not needed, could be simplified to 

 

SELECT ID,PHN AS CONTACT
FROM TEST
UNION
SELECT ID,MOB  AS CONTACT
FROM TEST
;

But both queries apply UNION DISTINCT which results in two redistribution steps plus a final sort eliminating duplicate rows.

 

Better move the CASE into WHERE and then you can switch to UNION ALL:

SELECT ID,PHN AS CONTACT
FROM TEST
UNION ALL
SELECT ID,MOB  AS CONTACT
WHERE PHN<>MOB
FROM TEST;

 

Re: sql help required

Try with below query..

 

SELECT id,phn as contact
FROM test
UNION ALL
SELECT id,mob as contact
FROM test
WHERE phn<>mob;