Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Hi,I am trying to flag with '1' if two rows of same ID has NOT NULL value, SOURCE TABLE:ID LINE COL 1 1 'A' 1 2 'B'2 1 NULL1 1 NULL3 1 'C'3 4 'D'Desired O/P:ID Flag1 12 03 1I want to group by ID and flag if it has a not Null value. Please let me know thanks in advance.
bitsmed
Aged Yak Warrior
545 Posts
Posted - 2012-12-17 : 15:48:26
try this:
select id ,sign(sum(case when col is null then 0 else 1 end)) as flag from thetable as a group by id order by id
edit:the above does not work as intended, try this instead:
select id ,case when sum(case when col is null then 0 else 1 end)>2 then 1 else 0 end as flag from thetable as a group by id order by id
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts
Posted - 2012-12-18 : 18:44:14
Slight variation:[CODE]select id, case when count(col) > 1 then 1 else 0 end flagfrom @MyTblgroup by idorder by id;[/CODE]The COUNT aggregate won't count NULL values. This potentially runs a little quicker.=================================================Hear the sledges with the bells - silver bells! What a world of merriment their melody foretells!
waterduck
Aged Yak Warrior
982 Posts
Posted - 2012-12-18 : 21:05:38
select ID, sum(distinct case when COL IS not null then 1 else 0 end)from @MyTblgroup by id