pivot

[geraldjr30b]

hi. i have the following:

SELECT distinct top 30 studentid,[101], [102], [103],[104]
FROM tbl_TEST s
PIVOT
(count(grade_status)
FOR semester IN
([101], [102], [103],[104]))p

How can I actually list the grade_status value instead of the count? Or how can i group by grade_status?

Thanks

 

[philhege]

If the grade_status recorded is 1-to-1 for student/semester, use MAX().

-----------
With business clients like mine, you'd be better off herding cats.

 

[geraldjr30b]

What if the grades are letters instead of numbers?

 

[dhookom]

geraldjr30b,
What happened when you tried it or did you automatically come back with a question?

Duane
Hook'D on Access
MS Access MVP

 

[geraldjr30b]

Would it be max(grade status) instead of count(grade status)?

 

[dhookom]

Did you try it? Remember to include the underscore and report back with your results.

Duane
Hook'D on Access
MS Access MVP

 

[philhege]

Would it be max(grade status) instead of count(grade status)?

Yes.

Now, remember my previous caveat: if the relationship among studentID, semester, and grade_status is truly 1-1-1, the statement should work.

Why?

Try this:

CREATE TABLE MyMaxTest
(myvalue char(1))
INSERT INTO MyMaxTest
SELECT 'A'

SELECT Max(myvalue) from MyMaxTest

The MAX() function reports the max value of the column. Since there's only one record, that's the value it reports.

Now try this (assuming you didn't whack the table):

INSERT INTO MyMaxTest
SELECT 'B'
SELECT Max(myvalue) from MyMaxTest

Now what do you get?

Of course, SQL performed the MAX() calculation, didn't it? So my caveat stands. You can "trick" the PIVOT statement into returning what you need, if and only if the uniqueness I stipulated applies across the fields.


-----------
With business clients like mine, you'd be better off herding cats.