what is query for this one?-CodePudding

I have a table with 3 columns id, homeph, mobileph.

If homeph is equal to mobileph, then homeph or mobileph with other line what is query for this?

CodePudding user response：

This should give you the desired result.

SELECT ID AS id, Homeph AS phone
FROM table

UNION

SELECT ID AS id, Contactph AS phone
FROM table

CodePudding user response：

In Oracle, you could use:

SELECT DISTINCT id, phone
FROM   input_table
UNPIVOT (
  phone FOR type IN (homeph, contactph) 
)

But it will be more efficient to check if the phone numbers are identical before unpivoting (as UNPIVOT will, by default, ignore NULL values):

SELECT id, phone
FROM   (
  SELECT id,
         homeph,
         CASE WHEN homeph = contactph THEN NULL ELSE contactph END AS contactph
  FROM   input_table
)
UNPIVOT (
  phone FOR type IN (homeph, contactph) 
)

Which, for the sample data:

CREATE TABLE input_table (id, homeph, contactph ) AS
  SELECT 1, 99999, 88888 FROM DUAL UNION ALL
  SELECT 2, 77777, 77777 FROM DUAL;

Both output:

ID	PHONE
1	99999
1	88888
2	77777

fiddle