ORACLE SQL - Categorizing records based on logic -

ORACLE SQL - Categorizing records based on logic -

what best way categorize records based on logic?

for example, table:

id house farm 1 (null) (null)

i output:

id missing 1 house 1 farm

aside obvious union below, there improve way? maybe case when? union not flexible when dealing bigger number of conditions.

select id, 'house' missing table house null union select id, 'farm' missing table farm null

while don't know if it's more efficient union all, alternative utilize unpivot depending on version of oracle using:

select id, missing ( select * yourtable unpivot include nulls (ismissing missing in (house 'house', farm 'farm')) ) t ismissing null

and here sql fiddle.

sql oracle