CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
Paul Zaczkowski
Paul Zaczkowski

Reputation: 2888

SQL Grouping with null rows

I've searched around a bit and couldn't find anything that addressed my exact issue

I have a table (MSSQL), and the data looks like this:

Source table:

RID    FullName FirstName   LastName    Email               Address
-------------------------------------------------------------------------------
1      NULL     [BOB]       NULL        NULL                NULL
1      NULL     NULL        [BOB]       NULL                NULL
1      NULL     NULL        NULL        [[email protected]]       NULL
2      NULL     [JAY]       NULL        NULL                NULL
2      NULL     NULL        [JAY]       NULL                NULL
2      NULL     NULL        NULL        [[email protected]]   NULL
3      NULL     [RICK]      NULL        NULL                NULL
3      NULL     NULL        [RICK]      NULL                NULL
3      NULL     NULL        NULL        [[email protected]]     NULL

But I need to transform the data to look like this:

Results table:

RID     FullName             Email                Address
---------------------------------------------------------
1       [BOB] [BOB]        [[email protected]]          NULL
2       [JAY] [JAY]        [[email protected]]      NULL
3       [RICK] [RICK]      [[email protected]]        NULL

NOTE: If Fullname from the source table is NULL, then we combine FirstName and LastName. (If both FirstName and LastName are NULL, then we simply have a NULL FullName result, which is completely fine).

Thanks in advance for any help on this!

Upvotes: 2

Views: 2073

Answers (2)

lc.
lc.

Reputation: 116458

Here's one way, although a bit convoluted. I do not know your full data set, but this should give you all possible combinations in case you have multiple entries for each RID (e.g. multiple rows with different FirstName values).

SELECT a.RID, b.FirstName + ' ' + c.LastName AS FullName, d.Email
FROM myTable a
LEFT OUTER JOIN myTable b ON a.RID = b.RID AND b.FirstName IS NOT NULL
LEFT OUTER JOIN myTable c ON a.RID = c.RID AND c.LastName IS NOT NULL
LEFT OUTER JOIN myTable d ON a.RID = d.RID AND d.Email IS NOT NULL
GROUP BY a.RID, b.FirstName, c.LastName, d.Email

SQL Fiddle example

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You need a group by:

select rid, coalesce(max(FullName), max(FirstName)+' '+max(LastName)) as FullName,
       max(Email) as Email,
       max(Address) as Address
from t
group by rid
order by 1

Upvotes: 4

Related Questions