Selecting distinct, non-null, unless null is the only value for that record combination (tsql)

Question

I have a table with Student ID, Service, and Provider. I want to show the DISTINCT Providers for each Service, but ONLY show NULL Providers if there is no other Provider for that Service and ID.

In other words, if a Student has a certain Provider and Service, I don't want to select where the Provider is NULL, unless that specific Student and Provider do not have another non-NULL Provider, in which case I DO want to select the NULL Provider row. I also don't want duplicates for the non-NULLS.

Here's a sample table:

ID  Service Provider  
1   SL      Joe  
1   SL      NULL  
2   Sped    Mary  
2   Sped    Jim  
2   Sped    NULL  
2   Sped    Mary  
3   SL      Larry  
3   OT      NULL  
3   SL      NULL

And what I'd like to get as a result of my select is:

ID  Service Provider  
1   SL      Joe  
2   Sped    Mary  
2   Sped    Jim  
3   SL      Larry  
3   OT      NULL

So, for example, Student 1 has a non-NULL and a NULL provider for Service "SL," so I just want to show the non-NULL provider, Joe. Student 2 has four "Sped" providers: Mary (twice), Jim, and NULL, so I only want to show Mary (once) and Jim. Student 3 has Service "SL" twice, with Larry and NULL, so I only want to show Larry. However, Student 3 has a NULL for "OT," and since there are no non-NULL values for that Student/Provider combination, I want to show the NULL value for that row.

This report is to show service providers where they've given the student a provider (a good thing) but also where students have a service without any provider (a bad thing.) My users are easily confused, so I need to present it this way. Thank you for any help!

Answer 1

try this (before OP said they were on SQL Server 2000):

--ONLY WORKS ON SQl Server 2005 and up
DECLARE @YourTable table (ID int, Service varchar(5), provider varchar(5))
SET NOCOUNT ON
INSERT INTO @YourTable VALUES (1,'SL'  ,'Joe')
INSERT INTO @YourTable VALUES (1,'SL'  ,NULL)
INSERT INTO @YourTable VALUES (2,'Sped','Mary')
INSERT INTO @YourTable VALUES (2,'Sped','Jim')
INSERT INTO @YourTable VALUES (2,'Sped',NULL)
INSERT INTO @YourTable VALUES (2,'Sped','Mary')
INSERT INTO @YourTable VALUES (3,'SL'  ,'Larry ')
INSERT INTO @YourTable VALUES (3,'OT'  ,NULL)
INSERT INTO @YourTable VALUES (3,'SL'  ,NULL)
SET NOCOUNT OFF

SELECT DISTINCT
    ID,Service,provider
    FROM (SELECT
              ID,Service,provider,ROW_NUMBER() OVER(PARTITION BY ID,Service ORDER BY ID,Service,Provider desc) AS Rank
              FROM @YourTable
         ) dt
    WHERE dt.provider IS NOT NULL OR dt.Rank=1
    ORDER BY ID,Service,provider

OUTPUT:

ID          Service provider
----------- ------- --------
1           SL      Joe
2           Sped    Jim
2           Sped    Mary
3           OT      NULL
3           SL      Larry

(5 row(s) affected)

EDIT version after OP said SQL Server 2000:

CREATE TABLE #YourTable (ID int, Service varchar(5), provider varchar(5))
SET NOCOUNT ON
INSERT INTO #YourTable VALUES (1,'SL'  ,'Joe')
INSERT INTO #YourTable VALUES (1,'SL'  ,NULL)
INSERT INTO #YourTable VALUES (2,'Sped','Mary')
INSERT INTO #YourTable VALUES (2,'Sped','Jim')
INSERT INTO #YourTable VALUES (2,'Sped',NULL)
INSERT INTO #YourTable VALUES (2,'Sped','Mary')
INSERT INTO #YourTable VALUES (3,'SL'  ,'Larry ')
INSERT INTO #YourTable VALUES (3,'OT'  ,NULL)
INSERT INTO #YourTable VALUES (3,'SL'  ,NULL)
SET NOCOUNT OFF


SELECT
    y.ID,y.Service,y.provider
    FROM #YourTable y
        INNER JOIN (SELECT
                        ID,Service,MAX(provider) AS MaxProvider
                        FROM #YourTable
                        GROUP BY ID,Service
                        HAVING MAX(provider) IS NOT NULL
                   ) dt ON y.ID=dt.ID AND y.Service=dt.Service
    WHERE provider IS NOT NULL
UNION
SELECT
    ID,Service,MAX(provider) AS MaxProvider
    FROM #YourTable
    GROUP BY ID,Service
    HAVING MAX(provider) IS  NULL
    ORDER BY ID,Service,provider

OUTPUT:

ID          Service provider
----------- ------- --------
1           SL      Joe
2           Sped    Jim
2           Sped    Mary
3           OT      NULL
3           SL      Larry
Warning: Null value is eliminated by an aggregate or other SET operation.

(5 row(s) affected)