Choosing a different, non-null value, unless null is the only value for this combination of records (tsql)
I have a table with student id, service and provider. I want to show 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 specific Provider and Service, I don't want to choose where the provider is NULL, unless that particular Student and Provider has another provider that is not NULL, in which case I do want to select the NULL Provider row. I also don't want duplicates for non-NULLS.
Here's an example 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 would like to receive as a result of my choice:
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 NULL and NULL provider for service "SL", so I just want to show a non-NULL provider Joe. Student 2 has four Sang providers: Mary (twice), Jim and NULL, so I want to show Mary (once) and Jim. Student 3 has Service "SL" twice, with Larry and NULL, so I just want to show Larry. However Student 3 has NULL for "OT" and since there are no non-NULL values for this Student / Provider combination, I want to show a NULL value for this row.
This report should show the service providers where they gave the student a provider (which is good), but also where the students have a service without any provider (which is a bad thing). My users get confused easily, so I need to Present it this way. Thanks for any help!
source to share
try this (before the OP said they are 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 after the 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)
source to share