amar_kaur16 (11/26/2012)
I have a dataset for students and they have either selected 'Math' or 'Science' or both.So I have 2 rows, 1 for Math and 1 for Science if they have selected both or 1 row if they have selected either of those 2 subjects.
Now I need to select data for those students who have selected only Math or both and I need to create a marker field (1,0) if they selected both then 1 else 0.
So 1 row for each student and if opted both then 1 in the marker field.
How can I do that?
Thanks,
Blyzzard
Kind of an odd requirement but this works for your description.
IF OBJECT_ID('TempDB..#MathScience','U') IS NOT NULL
drop table #MathScience
create table #MathScience
(
StudentID int,
SubjectChoice varchar(10)
)
insert #MathScience
select 1, 'Math' union all
select 1, 'Science' union all
select 2, 'Science' union all
select 3, 'Math'
select StudentID, Case when COUNT(*) > 1 then 1 else 0 end as Marker
from #MathScience
group by StudentID
having COUNT(*) > 1 or MIN(SubjectChoice) = 'Math'
Notice how I posted ddl and sample data. That is something you should do in the future to make things easier for us.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/