T-SQL Pivot Behavior Question, Pivot not behaving as expected with CAS Numbers

I have a database of analytical laboratory data, one analyte per record (MS SQL Server Express 2014 (12.0.2000)). I have successfully (more or less) gotten results that I expect using a chemical’s name. Chemical names, however, can vary from one lab to another. Example being trichloroethylene is the same as trichloroethene. Chemicals also have a Chemical Abstracts Service number (CAS Number) that is unique to the chemical, regardless of the synonyms used for that chemical and are of the format 99999-99-9, where the first series of digits can range from 2 to 5, from what I have seen. When I use the CAS Number all it returns are NULLS. Following are the two variations, first the successful one, then the one I really want to use, as the query should rely on the field that contains unique values.

SELECT pvt.StationName, pvt.SampleDate_D, pvt.Lab, 
     pvt.LabSampleID, Max(pvt.[Perfluorononanoic Acid]) AS PFNA, 
     Max(pvt.[Perfluorooctanoic Acid]) AS PFOA, 
     Max(pvt.[PerfluorooctaneSulfonic Acid]) AS PFOS, 
     Max(pvt.[PerfluorohexaneSulfonic Acid]) AS PFHxS, 
     Max(pvt.[PerfluorobutaneSulfonic Acid]) AS PFBS, 
     Max(pvt.[Perfluorohexanoic Acid]) AS PFHxA, 
     Max(pvt.[Hexafluoropropylene Oxide Dimer Acid]) 
     AS HFPODA
FROM (SELECT [StationName], [SampleDate_D], [SampleMatrix], 
            [LabSampleID], [ParameterName], [CASNumber], 
            [AnalyticMethod], IIF([Value] IS NULL, [Detect] * - 1, 
            [Value]) AS Result, [Detect], [Lab]
      FROM [dbo].[Analytical]
      WHERE AnalyticMethod = N'EPA 537 Version 1.1' AND 
            ((NOT (StationName LIKE N'LCS%')) AND 
            (SampleMatrix = 'Groundwater') AND 
            (NOT (StationName LIKE N'BLK%')))) 
     AS ST PIVOT (MAX(Result) FOR 
     ParameterName IN ([Perfluorononanoic Acid], 
     [Perfluorooctanoic Acid], [PerfluorooctaneSulfonic Acid], 
     [PerfluorohexaneSulfonic Acid], [PerfluorobutaneSulfonic Acid], 
     [Perfluorohexanoic Acid], 
     [Hexafluoropropylene Oxide Dimer Acid])) AS Pvt
GROUP BY StationName, SampleDate_D, Lab, LabSampleID;

This returns values where the chemical name is spelled the same as in the query, but there are multiple laboratories and they don’t spell the chemical names consistently. That is why I would prefer to use CAS Numbers.

Then the query relying on the CAS Number:

SELECT pvt.StationName, pvt.SampleDate_D, pvt.Lab, 
     pvt.LabSampleID, Max(pvt.[375-95-1]) AS PFNA, 
     Max(pvt.[335-67-1]) AS PFOA, 
     Max(pvt.[1763-23-1]) AS PFOS, 
     Max(pvt.[335-46-4]) AS PFHxS, 
     Max(pvt.[375-73-5]) AS PFBS, 
     Max(pvt.[307-24-4]) AS PFHxA, 
     Max(pvt.[13252-13-6]) 
     AS HFPODA
FROM (SELECT [StationName], [SampleDate_D], [SampleMatrix], 
            [LabSampleID], [ParameterName], [CASNumber], 
            [AnalyticMethod], IIF([Value] IS NULL, [Detect] * - 1, 
            [Value]) AS Result, [Detect], [Lab]
      FROM [dbo].[Analytical]
      WHERE AnalyticMethod = N'EPA 537 Version 1.1' AND 
            ((NOT (StationName LIKE N'LCS%')) AND 
            (SampleMatrix = 'Groundwater') AND 
            (NOT (StationName LIKE N'BLK%')))) 
     AS ST PIVOT (MAX(Result) FOR 
     ParameterName IN ([375-95-1], 
     [335-67-1], [1763-23-1], 
     [335-46-4], [375-73-5], 
     [307-24-4], 
     [13252-13-6])) AS Pvt
GROUP BY StationName, SampleDate_D, Lab, LabSampleID;

This query returns NULL for every entry. I am confused as I have seen multiple online examples where the pivoted fields contain numbers and they apparently work. CAS Numbers obviously aren’t numbers, but strings, and setting them off with [‘307-24-4′] or [N’307-24-4’] yields nothing different.

So, what obvious thing am I overlooking? In advance of any responses, I want to thank you for your time.

Dale

Answer

Based on your explanation the pivot in your second query should be:

PIVOT (MAX(Result) FOR 
     CASNumber IN ([375-95-1], 
     [335-67-1], [1763-23-1], 
     [335-46-4], [375-73-5], 
     [307-24-4], 
     [13252-13-6])) AS Pvt

Advertisement

Answer