CODEWITHSUNDEEP
sqlsql-servert-sql
DataDog
DataDog

Reputation: 525

Select rows with same ID, collapse rows with null / non-null values

I feel like as soon as I see this I'm going to smack myself but here goes:

Data:

CREATE TABLE testdata (
    id int,
    email varchar(25),
    street nvarchar(50),
    town nvarchar(15),
    zip nvarchar(5)
    source nvarchar(15))


INSERT INTO testdata (
    id,
    email,
    street,
    town,
    zip,
    source
)
VALUES
    (
        1,
        '[email protected]',
        null,
        'happy',
        null
    ),
    (
        1,
        '[email protected]',
        null,
        'happy',
        '19400'
    );

This yields:

-------------------------------------------------------------
|id |email              |street        |town        |zip    |
|1  |[email protected]  |happy place   |happy       |(null) |
|1  |[email protected]  |(null)        |happy       |19400  |

What I'm trying to arrive at is

|id |email              |street        |town        |zip    |
|1  |[email protected]  |happy place   |happy       |19400  |

Basically I want to collapse all the rows and grab anything that is not null where the ID is the same. If they are both null, then grab the null values. I attempted a self-join with a coalesce however that didn't work. I feel as if I'm close however is there a function out there that I can use that would make this easier?

Caveats: There can only be two rows with the same ID(Not sure if this makes it harder or easier). And If both rows are populated with information, I would want the first row.

Upvotes: 2

Views: 2414

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

There is no such thing as a "first" row in a table, unless you have a column that specifies the ordering. Your table does not have such a column.

So, the best that you can do is aggregation:

select email, max(street) as street, max(town) as town, max(zip) as zip
from testdata
group by email;

Upvotes: 4

Related Questions