CODEWITHSUNDEEP
sqloracle-database
user2077566
user2077566

Reputation: 21

ORACLE SQL - Categorizing records based on logic

What is the best way categorize records based on logic?

For example, from this table:

ID House  Farm
1  (null) (null)

I would like to output:

ID Missing
1  House
1  Farm

Aside from the obvious UNION all below, is there a better way? Maybe a case when? UNION ALL will not be easily flexible when dealing with a bigger number of conditions.

select ID, 'House' as Missing from table where house is null
union all 
select ID, 'Farm' as Missing from table where farm is null

Upvotes: 2

Views: 296

Answers (2)

TechDo
TechDo

Reputation: 18659

Please check the result using UNPIVOT. Check the links pivot and unpivot queries in 11g, PIVOT and UNPIVOT Operators in Oracle Database 11g Release 1 for more details.

SELECT 
  ID, MISSING
FROM  
(
    SELECT  ID, NVL(HOUSE, 1) HOUSE, NVL(FARM, 1) FARM FROM YourTable
 )x
UNPIVOT (
 DCol
 FOR MISSING
 IN  (HOUSE, FARM) 
);

or

SELECT 
  ID, MISSING
FROM YourTable 
UNPIVOT INCLUDE NULLS (
DCol
FOR MISSING
IN  (HOUSE, FARM) 
);

Upvotes: 1

sgeddes
sgeddes

Reputation: 62851

While I don't know if it's more efficient than UNION ALL, another option is to use UNPIVOT depending on the version of Oracle you are using:

SELECT ID, Missing
FROM (
    SELECT *
    FROM   YourTable
    UNPIVOT INCLUDE NULLS (IsMissing FOR Missing IN (House as 'House', Farm as 'Farm'))
  ) t
WHERE IsMissing IS NULL

And here is the SQL Fiddle.

Upvotes: 3

Related Questions