Convert row data into columns Access 07 without using PIVOT

Question

I am on a work term from school. I am not very comfortable using SQL, I am trying to get a hold of it....

My supervisor gave me a task for a user in which I need to take row data and make columns. We used the Crosstab Wizard and automagically created the SQL to get what we needed.

Basically, we have a table like this:

ReqNumber Year FilledFlag(is a checkbox) FilledBy 1 2012 (notchecked) ITSchoolBoy 1 2012 (checked) GradStudent 1 2012 (notchecked) HighSchooler 2 etc, etc.

What the user would like is to have a listing of all of the req numbers and what is checked

Our automatic pivot code gives us all of the FilledBy options (there are 9 in total) as column headings, and groups it all by reqnumber.

How can you do this without the pivot? I would like to wrap my head around this. Nearest I can find is something like:

SELECT SUM(IIF(FilledBy = 'ITSchoolboy',1,0) as ITSchoolboy, SUM(IIF(FilledBy = 'GradStudent',1,0) as GradStudent, etc. FROM myTable

Could anyone help explain this to me? Point me in the direction of a guide? I've been searching for the better part of a day now, and even though I am a student, I don't think this will be smiled upon for too long. But I would really like to know!

It's not that I would avoid using the pivot, it's just that as I am new to the language, I would like to learn how to do it another way. My boss told me he would do it a different way (the way noted above) becasue he is more comfortable with 'pure SQL'. I think I would just like to know to increase my knowledge is all (sorry for the awful English). — mrwienerdog
– mrwienerdog, Commented Apr 11, 2012 at 14:51
You guys - so observant (and clever!). We aren't using the year column, it's a table that is used to track student employment, so it is assumed it is for the current year (although the order may have been made in the last calendar year). — mrwienerdog
– mrwienerdog, Commented Apr 11, 2012 at 15:07

HansUp · Accepted Answer · 2012-04-11 15:33:43Z

I think your boss' suggestion could work if you GROUP BY ReqNumber.

SELECT ReqNumber, SUM(IIF(FilledBy = 'ITSchoolboy',1,0) as ITSchoolboy, SUM(IIF(FilledBy = 'GradStudent',1,0) as GradStudent, etc. FROM myTable GROUP BY ReqNumber;

A different approach would be to JOIN multiple subqueries. This example pulls in 2 of your categories. If you need to extend it to 9 categories, you would have a whole lot of joining going on.

SELECT itsb.ReqNumber, itsb.ITSchoolboy, grad.GradStudent FROM ( SELECT ReqNumber, FilledFlag AS ITSchoolboy FROM myTable WHERE FilledBy = "ITSchoolboy" ) AS itsb INNER JOIN ( SELECT ReqNumber, FilledFlag AS GradStudent FROM myTable WHERE FilledBy = "GradStudent" ) AS grad ON itsb.ReqNumber = grad.ReqNumber

Please notice I'm not suggesting you should use this approach. However, since you asked about alternatives to your pivot approach (which works) ... this is one. Stay tuned in case someone else offers a simpler alternative. :-)

Thanks a bunch, I just want to learn how these things work (in general). I will follow your advice and keep a watch. And I agree, it would be an awful lot of joining....
Makes a fairly persuasive case for the existence of pivot. :-) Pivot can make some challenges easier to handle, especially if you can use that wizard to guide you.
I was asking mostly in case I came up against this situation where PIVOT wasn't available (or especially the wizard - you don't learn much by using one of those, although they are obviously handy).
I used to avoid those hand-holdy wizardly flummies. Later I concluded that was short-sighted. Starting off with with what CrossTab Wizard gave me, then switching to SQL View gave me a better understanding of pivot details. So my revised policy is to use wizards where they can help, but not be addicted to them.
I totally agree, save that I like to understand what the Wizard is doing, and once I'm comfortable with that, I let it do the work for me. It's like mixing bread (previous career), I learned to mix dough by hand until I had a 'feel' for it, then when I learned how the dough should look, feel, and react, I started using a mixer.

Collectives™ on Stack Overflow

Convert row data into columns Access 07 without using PIVOT

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related