SQL Server - Query logic

First, COALESCE is your friend. Use the. With it, your query is simplified for this:

SELECT *, COALESCE(h, f, d, b, a, 0) AS y1, COALESCE(h, g, f, e, d, c, c, 0) AS y2
FROM valor

Notice that something is still wrong. In y1 , there are no fields g , e or c . In the y2 field, there is no b and a , and c appears twice.

COALESCE simplifies your problem and provides you with y2 promptly, but still does not resolve to y1 . A structure CASE can help you by identifying the first non-null field and then bringing the previous field.

The result is this:

SELECT
    *,
    CASE
        WHEN h IS NOT NULL THEN g
        WHEN g IS NOT NULL THEN f
        WHEN f IS NOT NULL THEN e
        WHEN e IS NOT NULL THEN d
        WHEN d IS NOT NULL THEN c
        WHEN c IS NOT NULL THEN b
        WHEN b IS NOT NULL THEN a
        ELSE 0
    END AS y1,
    COALESCE(h, g, f, e, d, c, b, a, 0) AS y2
FROM valor

Victor Stafusa, was show his answer! I could not remember the COALESCE function.

But yesterday, after posting my doubt, I had been able to solve the problem in a way that was different from yours, but with the same logic and reasoning using the IIF function instead of CASE . Getting the query like this:

select *,

iif(h is not null, g, iif(g is not null, f, iif(f is not null, e, iif(e is not null, d, iif(d is not null, c, iif(c is not null, b, iif(b is not null, a, '0'))))))) as y1,
isnull(h,isnull(g,isnull(f,isnull(e,isnull(d,isnull(c,isnull(b,0))))))) as y2
from valor

Now I can add the best of both queries.

By my response, you may already suspect that you have a database architecture problem ...

WITH q (valor, ordem, id) AS
(
    SELECT a valor, 1 ordem, id
    FROM tabela
    WHERE a IS NOT NULL

    UNION

    SELECT b, 2, id
    FROM tabela
    WHERE b IS NOT NULL

    UNION

    ....

    UNION

    SELECT h, 8, id
    FROM tabela
    WHERE h IS NOT NULL
)
SELECT valor, id, ordem
FROM q
JOIN (SELECT id, MAX(valor) maximo FROM q GROUP by id) r
    ON (q.valor = r.maximo OR q.valor = (r.maximo - 1))
    AND q.id = r.id
ORDER BY id, ordem DESC