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!
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)
