question

I wasn't sure if the ID would be the same in all rows, so here's an example that copes with multiple IDs declare @table table (id varchar(10), status char(1)) insert into @table select 'A0011','A' insert into @table select 'A0011','A' insert into @table select 'A0011','A' insert into @table select 'A0012','A' insert into @table select 'A0012','A' insert into @table select 'A0012','A' insert into @table select 'A0012','B' select id, case when max(dr) = 1 then 'Y' else 'N' end from (select id, dense_rank()over(order by status) dr from @table) t1 group by id This uses a dense_rank() function to simply count the number of statuses per id, and if the max value is not 1 then there must be a mix of statuses. [Note this will give the wrong answer if all statuses are 'N', but I'm not sure if thats a requirement or not?] Is there a limited set of values for Status? ---- edit: clarifiaction from OP this new version sums the 'A' as 0 and 'N' as 1, converts to a bit, and then selects 'Y' or 'N' based on that declare @table table (id varchar(10), status char(1)) insert into @table select 'A0011','A' insert into @table select 'A0011','A' insert into @table select 'A0011','A' insert into @table select 'A0012','A' insert into @table select 'A0012','A' insert into @table select 'A0012','A' insert into @table select 'A0012','N' insert into @table select 'A0013','N' insert into @table select 'A0013','N' insert into @table select 'A0013','N' select id, case cast(sum(case when status = 'N' then 1 else 0 end) as bit) when 1 then 'N' else 'Y' end from @table group by id

id would be same in all rows and it has to work when all the status are 'N' too. the status values can be 'A' or 'N' I had the below sql and it won't work when all the status are 'N'.. i couldn't figure it .. SELECT CASE WHEN (SELECT COUNT(*) FROM (SELECT DISTINCT * FROM #t3) t) = 1 THEN 'Y' ELSE 'N' END