May 2, 2012 at 4:21 pm
I have table schema and data as in attached.
I would like to group by each schoolyear, each schoolID, then list their school grade range served.
I use the below query:
SELECT sg.schoolyear, sg.schoolid,sg.GradeCd,g.GradeSortNbr FROM dbo.schSchoolGrade sg
INNER JOIN dbo.schSchool s
ON sg.schoolID=s.SchoolID AND sg.SchoolYear=s.SchoolYear
INNER JOIN dbo.schGrade g
ON sg.GradeCd=g.GradeCd AND sg.schoolyear=g.SchoolYear
AND sg.schoolyear=2012
WHERE sg.schoolid=203
ORDER BY g.GradeSortNbr
and I get the result as below:
schoolyearschoolidGradeCdGradeSortNbr
2012203K2
2012203013
2012203024
2012203035
2012203046
2012203057
But I want the result be
schoolyearschoolidGradeServed
2012 203 k-5
2012 204 k-5
.....
Basically to use the min GradesortNbr and max GradesortNbr , to get the values of gradeCd and combine them to one string.
Sorry attach the script again.
May 2, 2012 at 4:23 pm
Attachments missing..
Also can you please clarify th eexpected result ?
May 2, 2012 at 4:50 pm
Thanks, I just added the attachment.
May 2, 2012 at 4:55 pm
Can you reduce the number of rows in ur sample data? my SSMS hangs while i copy paste it 🙁
May 2, 2012 at 5:04 pm
Ok, here is the reduced size sample data.
May 2, 2012 at 7:24 pm
sqlfriends (5/2/2012)
Ok, here is the reduced size sample data.
You should try loading your own script and see what happens when you have no Carriage Returns or Line Feeds in the script especially with all those "GO"s. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 2, 2012 at 7:35 pm
Method 1 (Multiple Row Number)
; with base as
(
SELECT sg.schoolyear, sg.schoolid,sg.GradeCd,g.GradeSortNbr
, rn1 = row_number() over (partition by sg.schoolyear, sg.schoolid order by g.GradeSortNbr)
, rn2 = row_number() over (partition by sg.schoolyear, sg.schoolid order by g.GradeSortNbr desc)
FROM dbo.schSchoolGrade sg
INNER JOIN dbo.schSchool s
ON sg.schoolID=s.SchoolID AND sg.SchoolYear=s.SchoolYear
INNER JOIN dbo.schGrade g
ON sg.GradeCd=g.GradeCd AND sg.schoolyear=g.SchoolYear
--ORDER BY sg.schoolyear, sg.schoolid,g.GradeSortNbr,sg.GradeCd
)
select bs.SchoolID , bs.SchoolYear
,Grades =
MAX(case when bs.rn1 = 1 then bs.GradeCd else '' end ) + ' - ' +
MAX(case when bs.rn2 = 1 then bs.GradeCd else '' end )
from base bs
where bs.rn1 = 1 or bs.rn2 = 1
group by bs.SchoolID , bs.SchoolYear
Method 2 (Traditional Groupy BY Min Max)
; with base as
(
SELECT sg.schoolyear, sg.schoolid,sg.GradeCd,g.GradeSortNbr
FROM dbo.schSchoolGrade sg
INNER JOIN dbo.schSchool s
ON sg.schoolID=s.SchoolID AND sg.SchoolYear=s.SchoolYear
INNER JOIN dbo.schGrade g
ON sg.GradeCd=g.GradeCd AND sg.schoolyear=g.SchoolYear
--ORDER BY sg.schoolyear, sg.schoolid,g.GradeSortNbr,sg.GradeCd
)
, MinMax as
(
select t.SchoolYear , t.SchoolID ,Mn = MIN (t.GradeSortNbr)
,Mx = MAX(t.GradeSortNbr)
from base t
group by t.SchoolYear , t.SchoolID
)
select mm.SchoolID , mm.SchoolYear
,Grades =
MAX(case when bs.GradeSortNbr = mm.Mn then bs.GradeCd else '' end ) + ' - ' +
MAX(case when bs.GradeSortNbr = mm.Mx then bs.GradeCd else '' end )
from MinMax mm
join base bs
on bs.SchoolID = mm.SchoolID
and bs.SchoolYear = mm.SchoolYear
and ( bs.GradeSortNbr = mm.Mn or bs.GradeSortNbr = mm.Mx)
group by mm.SchoolID , mm.SchoolYear
Method 3 (Cross Apply)
; with base as
(
SELECT sg.schoolyear, sg.schoolid,sg.GradeCd,g.GradeSortNbr
FROM dbo.schSchoolGrade sg
INNER JOIN dbo.schSchool s
ON sg.schoolID=s.SchoolID AND sg.SchoolYear=s.SchoolYear
INNER JOIN dbo.schGrade g
ON sg.GradeCd=g.GradeCd AND sg.schoolyear=g.SchoolYear
--ORDER BY sg.schoolyear, sg.schoolid,g.GradeSortNbr,sg.GradeCd
)
, distVals as
(
select t.SchoolYear , t.SchoolID
from base t
group by t.SchoolYear , t.SchoolID
)
select otr.* , Grades = CrsApp1.GradeCd + ' - ' + CrsApp2.GradeCd
from distVals Otr
cross apply ( select top 1 t1.GradeCd
from base t1
where t1.SchoolID = Otr.SchoolID
and t1.SchoolYear = Otr.SchoolYear
order by t1.GradeSortNbr
) CrsApp1
cross apply ( select top 1 t2.GradeCd
from base t2
where t2.SchoolID = Otr.SchoolID
and t2.SchoolYear = Otr.SchoolYear
order by t2.GradeSortNbr desc
) CrsApp2
Edit: added row number method
May 2, 2012 at 11:14 pm
For anyone that is interesed in solving the problem but feeling sore due to OPs attachement, pfa the .sql file that has the table script, insert statements and my solutions (please note that i have cleansed OPs data to be readily executable)
May 3, 2012 at 9:08 am
Sorry, I didn't notice the schema.txt change the format. I did try the script to create the tables, and then I save it, but it didn't allow the file extension, so I changed to .txt, maybe at that time, it changed the file content and the format, sorry again.
I will take a look at the solution, thanks a lot
May 3, 2012 at 10:12 am
Thank you, I tried 3 methods, I like the first one best. then 2nd, then 3rd.
I can understand them, but just cannot think of how to make it like this at the beginning :(.
lack of practice and experience?
Is cross apply no longer available in SQL server 2008? Thanks
Also in method 2, when using
select t.SchoolYear , t.SchoolID ,Mn = MIN (t.GradeSortNbr)
,Mx = MAX(t.GradeSortNbr)
from base t
group by t.SchoolYear , t.SchoolID
when group by the schoolyear, and schoolid, will the column GradeSortNbr automatically sorted even I don't specifically use order by clause which I am not allowed to use it in CTE.
Thanks
May 3, 2012 at 11:13 am
sqlfriends (5/3/2012)
I can understand them, but just cannot think of how to make it like this at the beginning :(.lack of practice and experience?
Yep, Practice and experience can only teach you the nuances and intricacies of t-sql, as with one's life 😉
If you have any doubts, please let us know, we will try dissecting the code into segments, so u could understand it better
sqlfriends (5/3/2012)
Is cross apply no longer available in SQL server 2008? Thanks
CROSS APPLY (and OUTER APPLY) is available starting SQL 2005 only.so, of course it IS available in 2008 (last i checked, it is present in 2012 also) 😎
sqlfriends (5/3/2012)
Also in method 2, when usingselect t.SchoolYear , t.SchoolID ,Mn = MIN (t.GradeSortNbr)
,Mx = MAX(t.GradeSortNbr)
from base t
group by t.SchoolYear , t.SchoolID
when group by the schoolyear, and schoolid, will the column GradeSortNbr automatically sorted even I don't specifically use order by clause which I am not allowed to use it in CTE.
You dont need an Order by clause inside CTE. SQL will arrange the data in order for it to be used in JOINs. Only, if your final SELECT needs ordering, you must add ORDER BY at the very end of the query.
May 3, 2012 at 12:04 pm
Thanks.
I created this as a view, based on this view I create a another view that joins some other tables, performance wise the second method and third one runs a little faster than the first one, I guess the reason could be more sorts in the first one.
Additional question, when I create views, is it better practice to make it schema bind instead of not ?
If I make it schema bind, everytime I alter the view or table, it will say
Cannot ALTER 'adw.vw_SchoolGradeServed' because it is being referenced by object 'vw_SchoolData
I have to drop the view and recreate it.
Would like to know what is the best pracive.
thanks
May 3, 2012 at 12:06 pm
Schema binding has its own advantages and disadvantages.. But whn u create a view, how ofter do u alter it? You wouldnt right? then i think, schema binding can be pretty handy for u
May 3, 2012 at 12:10 pm
ColdCoffee (5/3/2012)
Schema binding has its own advantages and disadvantages.. But whn u create a view, how ofter do u alter it? You wouldnt right? then i think, schema binding can be pretty handy for u
As you also noted, it will prevent changes to the underlying table(s) that would affect the view as well.
May 3, 2012 at 12:12 pm
You are right, thanks for all the help.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply