Decode for multiple rows and columns in Oracle SQL

Question

I have a table(details)-

Name(primary key)	color	shading	available
A	Y	N	N
B	N	Y	Y
C	N	N	N

Now, I want a query that checks rows as well as columns one by one for each record. For eg,

if color = "Y" then output = "ABC"
if color = "N" and shading = "Y" then output = "XYZ"
if color = "N" and shading = "N" and available = "Y" then output = "Hurray!"

Priority for column goes like - color>shading>available So, if color = "Y" we don't need to look at other 3 columns and give the output as "ABC" Similarly, it should check output values for all the rows and eventually only give only one output according to priority. Priority goes as follows - ABC>XYZ>Hurray! i.e. if any of the 3 rows gives output as "ABC" final output should be "ABC". But if 2 rows give output "Hurray!" and one row gives output "XYZ" final output should be "XYZ".

I tried using decode.

SELECT DECODE (color,'Y','ABC') AS RESULT_color,
DECODE (shading,'Y','XYZ') AS RESULT_shade,
DECODE (available,'Y','Hurray!') AS RESULT_available from details;

But this is wrong because it only considers one column and one row at a time when I want to check all the columns and all the rows together.

Answer 1

SQL query while selecting threats every row separately (most of the time), so CASE, DECODE and other functions and expression will work with one row at a time.

You, as I understand, want to analyze table as a whole. It seems like not really SQL's task, but you could "hack" your way out.

You can analyze rows one at a time, and then calculate output for whole table.

Here query:

1. based on values in columns assigns priority to rows,
2. gets maximum priority of rows,
3. decodes this maximum value into desired strings.

select
    DECODE (max(priority),
        3, 'ABC',
        2, 'XYZ',
        1, 'Hurray!',
        'BAD!')
from (
    select
        CASE
            WHEN (color = 'Y') THEN 3
            WHEN (
                color = 'N'
                AND shading = 'Y'
            ) THEN 2
            WHEN (
                color = 'N'
                AND shading = 'N'
                AND available = 'Y'
            ) THEN 1
            ELSE 0
        END priority
    from details
)