Removing NULL Value From Query Results

eusanpe · May 31st, 2009, 07:38 PM

Hello All,

I wrote this query but the results aren't what I expected. I have a bunch of NULL values displayed when there are no NULL values in the table.

Here is my query:

Code:

SELECT
(case when l.name = 'Sodium' then l.name end) as na_name, 
(case when l.name = 'Sodium' then l.value end) as na_value,
(case when l.name = 'Sodium' then l.valuedate end) as na_date,
(case when l.name = 'Postassium' then l.name end) as k_name, 
(case when l.name = 'Potassium' then l.value end) as k_value,
(case when l.name = 'Potassium' then l.valuedate end) as k_date,
(case when l.name = 'Chloride' then l.name end) as cl_name, 
(case when l.name = 'Chloride' then l.value end) as cl_value,
(case when l.name = 'Chloride' then l.valuedate end) as cl_date

FROM lab_table l
WHERE l.name in ('Sodium', 'Potassium', 'Chloride')
and l.valuedate between '20090101' and '20090531'

What displays is:

Quote:

na_name na_value na_valuedate k_name k_value k_valuedate cl_name cl_value cl_valuedate
------------------------------------------------------------------------------------------------------------------------
NULL NULL NULL NULL NULL NULL NULL NULL NULL
Sodium 140 20090501 11:00 NULL NULL NULL NULL NULL NULL
NULL NULL NULL Potassium 3.7 20090501 11:00 NULL NULL NULL
NULL NULL NULL NULL NULL NULL Chloride 105 20090501 11:00

I am sorry about the formatting of this. I also just posted one set of three names and values. The three tests, Sodium, Potassium and Chloride, are run at the same time so the ValueDate will always be the same for the group of three.

I would like the name, value and valuedate for all three items to display on one line without all of the null values.

Code:

Sodium  140  20090501 11:00 AM  Potassium  3.7  20090501 11:00 AM  Chloride  105  20090501 11:00 AM
Sodium  141  20090502 11:00 AM  Potassium  4.0  20090502 11:00 AM  Chloride  100  20090502 11:00 AM
Sodium  137  20090503 11:00 AM  Potassium  3.5  20090503 11:00 AM  Chloride  101  20090503 11:00 AM

How can I do this?

Thanks.

Code:


USE TempDB
GO

CREATE TABLE dbo.labtable
(      Name          VARCHAR(50),
       Value         VARCHAR(50),
        Valuedate     DateTime)
INSERT INTO labtable
(Name,Value,Valuedate)
SELECT 'Sodium','140', '2009-05-01' UNION ALL
SELECT 'Potassium','3.7', '2009-05-01' UNION ALL
SELECT 'Chloride','105', '2009-05-01' UNION ALL
SELECT 'Sodium','141', '2009-05-02' UNION ALL
SELECT 'Potassium','4.0', '2009-05-02' UNION ALL
SELECT 'Chloride','100', '2009-05-02' UNION ALL
SELECT 'Sodium','137', '2009-05-03' UNION ALL
SELECT 'Potassium','3.5', '2009-05-03' UNION ALL
SELECT 'Chloride','101', '2009-05-03'

Jeff Moden · May 31st, 2009, 07:59 PM

You need something to group by or each row will be treated individually, which causes the nulls...

SELECT
CONVERT(CHAR(10),l.ValueDate,120)AS Sample_Date,
MAX(casewhen l.name ='Sodium'then l.name end)as na_name,
MAX(casewhen l.name ='Sodium'then l.value end)as na_value,
MAX(casewhen l.name ='Potassium'then l.name end)as k_name,
MAX(casewhen l.name ='Potassium'then l.value end)as k_value,
MAX(casewhen l.name ='Chloride'then l.name end)as cl_name,
MAX(casewhen l.name ='Chloride'then l.value end)as cl_value
FROM labtable l
WHERE l.name in('Sodium','Potassium','Chloride')
and l.valuedate between'20090101'and'20090531'
GROUPBY ValueDate

My question would be, why isn't the following good enough so that you don't have columns of repeating data, null or not?

SELECT
CONVERT(CHAR(10),l.ValueDate,120)AS Sample_Date,
MAX(casewhen l.name ='Sodium'then l.value end)as [Sodium(NA)],
MAX(casewhen l.name ='Potassium'then l.value end)as [Potassium(K)],
MAX(casewhen l.name ='Chloride'then l.value end)as [Clorine(CL)]
FROM labtable l
WHERE l.name in('Sodium','Potassium','Chloride')
and l.valuedate between'20090101'and'20090531'
GROUPBY ValueDate

eusanpe · May 31st, 2009, 08:36 PM

Honesty..I like your way better. It is much cleaner

Thank you.

Jeff Moden · June 1st, 2009, 01:18 AM

Heh... cool. Just don't mispell Chlorine like I did.