SQL How-to question

[davepkz]

Suppose I have the following data:

student quarter grade
------- ------- -----
Jones 1 A
Jones 2 B+
Jones 3 A
Jones 4 B-
Smith 1 B+
Smith 2 B
Smith 3 B
Smith 4 B-


....and I want a SELECT statement to output something like this:

student Q1grade Q2grade Q3grade Q4grade
------- ------- ------- ------- -------
Jones A B+ A B-
Smith B+ B B B-

Any ideas on how to do such a thing in SQL?
many thanks
Dave

 

[Jason Lepack]

Assuming that in every quarter there is only one grade for each
student, you could create a Crosstab Query.

I called the table "tbl_grades" and here is the SQL.

TRANSFORM First(tbl_grades.grade) AS QGrade
SELECT tbl_grades.student
FROM tbl_grades
GROUP BY tbl_grades.student
PIVOT tbl_grades.quarter;

Cheers,
Jason Lepack

 

[Erland Sommarskog]

Suppose I have the following data:

student quarter grade
------- ------- -----
Jones 1 A
Jones 2 B+
Jones 3 A
Jones 4 B-
Smith 1 B+
Smith 2 B
Smith 3 B
Smith 4 B-


...and I want a SELECT statement to output something like this:

student Q1grade Q2grade Q3grade Q4grade
------- ------- ------- ------- -------
Jones A B+ A B-
Smith B+ B B B-

Any ideas on how to do such a thing in SQL?
many thanks

In SQL Server (and ANSI SQL) you can do:

SELECT student,
MAX(CASE quarter WHEN 1 THEN grade END) AS AS Q1grade,
MAX(CASE quarter WHEN 2 THEN grade END) AS AS Q2grade,
MAX(CASE quarter WHEN 3 THEN grade END) AS AS Q3grade,
MAX(CASE quarter WHEN 4 THEN grade END) AS AS Q4grade
FROM tbl
GROUP BY student


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Van T. Dinh]

If this is done in Access, check Access Help on Crosstab Queries ...

 

[strawberry]

student quarter grade
------- ------- -----
Jones 1 A
Jones 2 B+
Jones 3 A
Jones 4 B-
Smith 1 B+
Smith 2 B
Smith 3 B
Smith 4 B-


...and I want a SELECT statement to output something like this:

student Q1grade Q2grade Q3grade Q4grade
------- ------- ------- ------- -------
Jones A B+ A B-
Smith B+ B B B-

You could also do this (although that transform method looks
smarter...)

SELECT g.student,q1.grade q1grade,q2.grade q2grade,q3.grade
q3grade,q4.grade q4grade FROM grades g
LEFT JOIN grades q1 ON q1.student = g.student AND q1.quarter =1
LEFT JOIN grades q2 ON q2.student = g.student AND q2.quarter=2
LEFT JOIN grades q3 ON q3.student = g.student AND q3.quarter=3
LEFT JOIN grades q4 ON q4.student = g.student AND q4.quarter=4
GROUP BY g.student

 

[Walt]

Suppose I have the following data:

student quarter grade
------- ------- -----
Jones 1 A
Jones 2 B+
Jones 3 A
Jones 4 B-
Smith 1 B+
Smith 2 B
Smith 3 B
Smith 4 B-


...and I want a SELECT statement to output something like this:

student Q1grade Q2grade Q3grade Q4grade
------- ------- ------- ------- -------
Jones A B+ A B-
Smith B+ B B B-

Any ideas on how to do such a thing in SQL?
many thanks
Dave

It's called crosstabulation, or crosstab for short. A quick web search on
crosstab SQL query should give you some pointers.

 

[davepkz]

Thanks to all. The examples you provided were exactly what I needed.
Dave