Don't return results for a particular condition

1

Attempting to create a query that will return all rows except the two rows noted in the screenshot below. The goal, in essence, is to ensure at least one row is returned for each ID value and at the same time remove rows where data1 is blank or nul. Hopefully, this makes sense.

  • If ID contains a duplicate value and one of its associated data1 values is blank or null, then don't return the row where data1 is blank or null.

  • If ID contains a duplicate value and all of its associated data1 values are blank or null, then return one of the rows where data 1 is blank or null.

For example
image

4

hi

please see if this works ??

drop create sample data .. click arrow which is to the left 
drop table #sampledata 

create  table #sampledata 
(
id int null ,
data1 varchar(10),
data2 varchar(10),
comments varchar(100)
)
go 

insert into #sampledata values 
(1,'aaa','aa','include in results'),
(1,'bbb','bb','include in results'),
(2, null,'cc','dont include in results'),
(2,'ccc','dd','include in results'),
(3, null,'ee','dont include in results'),
(3, ' ' ,'ff','include in results'),
(4, ' ' ,'gg','include in results'),
(5, 'ddd','hh','include in results')

select * from #sampledata

;with cte_rn as 
(
	select id , sum(case replace(isnull(data1,'1'),' ','1') when  '1' then 0 else 1 end)  as ok from #sampledata
	group by id 
)
select  a.* from #sampledata a where a.id in ( select id from cte_rn where ok >0 ) and (a.data1 is not null or a.data1 not like '% %')
union all 
select id ,data1 ,data2 ,comments  from (select  row_number() over(partition by id order by id ) as rn ,a.* from #sampledata a where a.id in ( select id from cte_rn where ok = 0 ) 
) q where q.rn = 1 
go
5 
;WITH cte_id_profile AS (
    SELECT id, MIN(data1) AS data1_min, MAX(data1) AS data1_max, COUNT(*) AS data1_count
    FROM #sampledata
    GROUP BY id
)
SELECT ca1.*
FROM cte_id_profile cip
CROSS APPLY (
    SELECT TOP (1) *
    FROM #sampledata sd
    WHERE sd.id = cip.id AND
        cip.data1_max IS NULL OR cip.data1_max = ''
    ORDER BY NEWID()
    UNION ALL
    SELECT *
    FROM #sampledata sd
    WHERE sd.id = cip.id AND
        cip.data1_max <> '' AND
        sd.data1 <> ''
) AS ca1
6

Thanks guys, next time I'll include a "create table" with sample data to streamline process. I'm going to take some time here deciphering the queries to get a basic understanding of how they work.