CODEWITHSUNDEEP
sqlhive
Nk.Pl
Nk.Pl

Reputation: 131

SQL Select Return Rows If Column Contain Value or Null Value Base on Key Columns

It was a bit difficult to describe my requirements based on the title, however I'll post with a table sample and result expectation.

I have a table (lets call it TBL_K) that looks like this:

KEY1    KEY2    VALUE1   VALUE2
abc     123     NULL     NULL
abc     123     9999     1111
abc     123     9999     1111
ghd     123     NULL     NULL
ghd     123     NULL     NULL
tiy     134     4444     NULL
tiy     134     4444     NULL
hhh     981     NULL     NULL

I want my Select statement to return the result in: 

KEY1    KEY2    VALUE1   VALUE2
abc     123     9999     1111
ghd     123     NULL     NULL
tiy     134     4444     NULL
hhh     981     NULL     NULL

I have came up with own solution with creating two sub-tables with a left outer join but I want to see if there are other ways of creating this result.

Upvotes: 0

Views: 74

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

It seems nearly to use max() :

select key1, key2, max(val1), max(val2)
from TBL_K tk
group by key1, key2;

Upvotes: 3

Nk.Pl
Nk.Pl

Reputation: 131

SELECT
    A.KEY1,
    A.KEY2,
    B.VALUE1,
    B.VALUE2
FROM
    (
        SELECT
            Z.KEY1,
            Z.KEY2,
            TRIM(Z.VALUE1) VALUE1,
            TRIM(Z.VALUE2) VALUE2
        FROM
            TBL_K Z
        WHERE
            TRIM(Z.VALUE1) IS NULL
        GROUP BY
            Z.KEY1,
            Z.KEY2,
            Z.VALUE1,Z.VALUE2) A LEFT OUTER JOIN 
    (
        SELECT
            Y.KEY1,
            Y.KEY2,
            TRIM(Y.VALUE1) VALUE1,
            TRIM(Y.VALUE2) VALUE2
        FROM
            TBL_K Y
        WHERE
            TRIM(Y.VALUE1) IS NOT NULL
        GROUP BY
            Y.KEY1,
            Y.KEY2,
            Y.VALUE1,Y.VALUE2) B
ON
    (A.KEY1 = B.KEY1
AND A.KEY2 = B.KEY2)

Upvotes: 0

Related Questions