Tuesday, October 14, 2003 | |
Source: Stephen Forte's WebBlog
Ok I have gotten a lot of press because I love the Rozenshtein Method for creating cross tab queries. I have even traveled the world and spoke about it all summer at TechEds around the world. I have gotten flack, especially from annoying Australians, about how “complex” the Rozenshtein Method is. So today I found myself writing a crosstab and used the Case Method. It was an ad-hoc query that I have to run a few times over the next few weeks. It never has to run in another database like Access or Oracle. It was needed to be quick and dirty. I decided NOT to use the Boolean aggregates and use a Case statement. The basic structure of a case statement is as follows: CASE FieldName WHEN ValueYouAreChecking THEN TrueExpression ELSE FalseExpression END Pretty easy no? Here is a sample: SELECT tlkpWeekEnding.WeekEnding_DT as Weekending, DiceCat.DiceCat_NM as 'Job Category', Sum(tblData.TotalListings) AS 'Total Listings', SUM(CASE Service_ID WHEN 1 THEN TotalListings ELSE 0 END) AS HotJobsTotal, SUM(CASE Service_ID WHEN 2 THEN TotalListings ELSE 0 END) AS MonsterTotal, SUM(CASE Service_ID WHEN 3 THEN TotalListings ELSE 0 END) AS CareerBuilderTotal FROM DiceCat INNER JOIN ((tlkpWeekEnding INNER JOIN (trelServiceURL INNER JOIN tblData ON trelServiceURL.URL_ID = tblData.URL_ID) ON tlkpWeekEnding.WeekEnding_ID = tblData.WeekEnding_ID) INNER JOIN WHERE tlkpWeekEnding.WeekEnding_ID=75 GROUP BY tlkpWeekEnding.WeekEnding_DT, DiceCat.DiceCat_NM Order by DiceCat.DiceCat_NM Here are the results, we move rows to columns:
So if this is so easy why oh why do I insist on using the Rozenshtein Method? Well, I don’t insist on using it all the time. I like it because it is super fast and make sense to me (I was into Math as a kid, sorry). I think that you should know both and use the one that you think is most effective for the job at hand. Happy Crosstabbing! [Stephen Forte's WebBlog] 7:11:53 PM trackback [] Articulate [] |