Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to create a marker field based on data Expand / Collapse
Author
Message
Posted Monday, November 26, 2012 1:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 20, 2014 2:51 PM
Points: 32, Visits: 91
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


Post #1388805
Posted Monday, November 26, 2012 1:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 13,085, Visits: 12,549
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1388813
Posted Monday, November 26, 2012 2:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 20, 2014 2:51 PM
Points: 32, Visits: 91
Could you please explain?

Thanks,
Blyzzard
Post #1388839
Posted Monday, November 26, 2012 2:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 13,085, Visits: 12,549
amar_kaur16 (11/26/2012)
Could you please explain?

Thanks,
Blyzzard


What part don't you understand? I am not even really sure if this meets your criteria.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1388850
Posted Monday, November 26, 2012 2:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 20, 2014 2:51 PM
Points: 32, Visits: 91
Actually I need to select all the fields and rows for Math subject and craete a marker so that if any student has selected Science as well then I it should be 1.
So basically I need to get Math data but need to indicate that some of them has selected both.

Post #1388854
Posted Monday, November 26, 2012 2:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 13,085, Visits: 12,549
That is what the query I posted will show you.

Here is another way to get the same results, but it has a bit more complicated execution plan.

select StudentID, Case when COUNT(*) > 1 then 1 else 0 end as Marker
from #MathScience
where StudentID in (select StudentID from #MathScience where SubjectChoice = 'Math')
group by StudentID

If there is something missing then you will need to post YOUR ddl, sample data and desired output. The stuff I am working with is because you didn't provide so I had to make some up.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1388859
Posted Monday, November 26, 2012 3:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 20, 2014 2:51 PM
Points: 32, Visits: 91
Your query is selecting all the students who have selected Math only but the Marker field is empty for all the returned students.
Thus I think that students who have selected both are not being included

Below is the input format
Student Id Name Subject Selection Date
10021 john Math 11/1/2011
10021 John Science 11/12/2011
10022 Becky Math 11/2/2011
10022 Becky Science 11/10/2011
10023 Will Math 11/4/2011
10024 Matt Math 11/5/2011

Output desired:

Student Id Name Subject Selection Date Marker ( Math& Science)
10021 john Math 11/1/2011 1
10022 Becky Math 11/2/1011 1
10023 Will Math 11/4/2011 0
10024 Matt Math 11/5/2011 0

I appreciate your help.
Thanks,
Blyzzard
Post #1388872
Posted Tuesday, November 27, 2012 7:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 13,085, Visits: 12,549
amar_kaur16 (11/26/2012)
Your query is selecting all the students who have selected Math only but the Marker field is empty for all the returned students.
Thus I think that students who have selected both are not being included

Below is the input format
Student Id Name Subject Selection Date
10021 john Math 11/1/2011
10021 John Science 11/12/2011
10022 Becky Math 11/2/2011
10022 Becky Science 11/10/2011
10023 Will Math 11/4/2011
10024 Matt Math 11/5/2011

Output desired:

Student Id Name Subject Selection Date Marker ( Math& Science)
10021 john Math 11/1/2011 1
10022 Becky Math 11/2/1011 1
10023 Will Math 11/4/2011 0
10024 Matt Math 11/5/2011 0

I appreciate your help.
Thanks,
Blyzzard


It seems you did not run the query I posted. And you really need to look at the link in my signature about how to post data. The format you posted is not helpful. Here is your data in a consumable format. I added Name to both of the queries I posted and they BOTH return exactly what you are looking for. I removed the selection date from the results because it doesn't make sense, which one will be in the result set when there are 2?

IF OBJECT_ID('TempDB..#NewMathScience','U') IS NOT NULL
drop table #NewMathScience

create table #NewMathScience
(
StudentID int,
Name varchar(20),
SubjectChoice varchar(20),
SelectionDate datetime
)

insert #NewMathScience
select 10021, 'john', 'Math', '2001-11-01' union all
select 10021, 'John', 'Science', '2011-11-12' union all
select 10022, 'Becky', 'Math', '2011-11-02' union all
select 10022, 'Becky', 'Science', '2011-11-10' union all
select 10023, 'Will', 'Math', '2011-11-04' union all
select 10024, 'Matt', 'Math', '2011-11-05'


select StudentID, Name, Case when COUNT(*) > 1 then 1 else 0 end as Marker
from #NewMathScience
group by StudentID, Name
having COUNT(*) > 1 or MIN(SubjectChoice) = 'Math'

select StudentID, Name, Case when COUNT(*) > 1 then 1 else 0 end as Marker
from #NewMathScience
where StudentID in (select StudentID from #NewMathScience where SubjectChoice = 'Math')
group by StudentID, Name



_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1389191
Posted Tuesday, November 27, 2012 8:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 175, Visits: 547
Wow Sean,
10 points for effort and patience!




For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1389240
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse