CODEWITHSUNDEEP
sqlms-accessms-access-2007
mrwienerdog
mrwienerdog

Reputation: 825

Convert row data into columns Access 07 without using PIVOT

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!

Upvotes: 1

Views: 1110

Answers (1)

HansUp
HansUp

Reputation: 97101

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. :-)

Upvotes: 1

Related Questions