How to eliminate NULL values from the query result?

September 5, 2022

Currently I’m trying to write a single SQL query to produce some intended result, I managed to do it but there are some null values in my results and I’m just wondering how can I get rid of them.

Here is the database structure:

Here is my query:

SELECT ut.username,
       (case when FieldID = 1 then Data end) as phone,
       (case when FieldID = 2 then Data end) as email
FROM usertable ut
JOIN userdata ud
ON ut.ID = ud.userID;

Here is my result:

Here is the intended result:

Any suggestions are welcomed!

>Solution :

Use a pivot to get just one row per user.

SELECT ut.username,
       MAX(case when FieldID = 1 then Data end) as phone,
       MAX(case when FieldID = 2 then Data end) as email
FROM usertable ut
JOIN userdata ud
ON ut.ID = ud.userID
GROUP BY ut.username;