SQL select: two-dimensional select with variable column count

Question

CREATE TABLE activities(activityid, name);
CREATE TABLE activity_scores(activityid, studentid, score);   
CREATE TABLE students (id, name);

Is there any way to write a single SELECT query that will produce one result for each student, in this format?

studentid | studentname | activity1_score | activity2_score | activity3_score [...]

It's trivial to do it with multiple queries:

for each studentid in "select id from students":
    print studentid
    for each activity in "select activityid from activities":
        select score from activity_scores where studentid = studentid
        print score

(pseudocode, I know it's not accurate)

Surely there's a way to create this result with a single SELECT query, right?

Answer 1

You're basically looking for a pivot table. If you just want to use pure ANSI SQL, then there's no way to do this. SQL only generates result sets with a predictable number of columns (not counting select *'s).

However, there may be a technology-specific way to do it. Which database engine are you using? SQL Server 2005 has the ability to do a pivot table.

Answer 2

If the activities are known and can be specified in the query, then its relatively easy, if a little verbose, assuming your SQL dialect supports it. For example:

SELECT s.id, s.name,
  (SELECT score FROM activity_scores as JOIN activities a ON a.activityid = as.activityid WHERE studentid = s.id AND a.activityname = 'Basketball') basketball_score,
  (SELECT score FROM activity_scores as JOIN activities a ON a.activityid = as.activityid WHERE studentid = s.id AND a.activityname = 'Football') basketball_score,
  ...

Basically this is called a crosstab query. If you want to do it dynamically then its harder and you'll probably need to resort to either code or a stored procedure so will depend on your database. Here is an example using SQL Server.

Answer 3

MySQL, SQLite, and perhaps other RDBMSs have something called GROUP_CONCAT,
which should do something like what you want (not tested -- don't know your join condition on the activity_scores table):

SELECT   students.studentid
         , students.studentname
         , GROUP_CONCAT(activity_scores.score)

FROM     activity_scores 
         INNER JOIN activities 
         ON activities.activityid = activity_scores.activityid
         INNER JOIN students 
         ON students.studentid = activities.studentid

GROUP BY students.studentid 
         , students.studentname

but smarter folks than me might say that doing something like this confuses two distinct steps which are easiest to deal with when left separate:

• data access / gathering
• data presentation

Answer 4

This result set shape offends relational algebra (on which SQL is based). See SQL antipattern post #2 http://stackoverflow.com/questions/346659/what-are-the-most-common-sql-anti-patterns/346850#346850

You should issue this query and format the results on the client:

SELECT s.name, a.name, x.score
FROM Activity_Score as x
  JOIN Students s
  ON x.StudentID = s.StudentID
  JOIN Activity a
  ON x.ActivityID = a.ActivityID
ORDER BY s.name, a.name, x.score

Answer 5

there was a conversation on this way back in 2003 on a precursor to SO here. The outcome of that thread agrees with the opinion that you would need to resort to an abstraction layer rather than perform it in a single query.

Answer 6

One possible way to fake this result is by aliasing the activity_scores table once for each activity.

select students.id as studentid, students.name as studentname,
    s1.score as activity1_score, s2.score as activity2_score,
    s3.score as activity3_score, s4.score as activity4_score...
from students, activity_scores as s1, activity_scores as s2,
    activity_scores as s3, activity_scores as s4...
where students.id = s1.studentid
    and students.id = s2.studentid
    and students.id = s3.studentid
    and students.id = s4.studentid...

I don't claim that this is in any way efficient, and this assumes that there is a fixed and finite number of activities associated with each and every student. With this version, students that are missing scores in any of the activities don't appear at all in the result set.

To allow for partial fulfillment of activity scores by some students, the joins have to be replaced with left joins.

-- T-SQL
...
where students.id *= s1.studentid
    and students.id *= s2.studentid
    and students.id *= s3.studentid
    and students.id *= s4.studentid...

-- standard SQL
...
from students LEFT JOIN activity_scores as s1 ON students.id = s1.studentid
    LEFT JOIN activity_scores as s2 ON students.id = s2.studentid
    LEFT JOIN activity_scores as s3 ON students.id = s3.studentid
    LEFT JOIN activity_scores as s4 ON students.id = s4.studentid...

I generally prefer to do a straight join (multiple records per student, one per activity score) and then build the resulting single line record using programming at the destination to do the "foreach" style processing that you list above, but I know that is not always possible.

Answer 7

One of the most frequently asked questions on SO.

The reason this is not supported in ANSI SQL is that the result set is not well-defined - it will have an arbitrarily varying number of columns.

However, knowing the number of columns, one can generate code to do it, the example solution I always give generates code for SQL Server 2005 using the PIVOT operator (the column list is static, which is why this needs to be done with dynamic SQL) using dynamic SQL and then executes it.

Answer 8

In SQL Server 2005/2008 you can try returning the activities/scores for each student as an xml set. Not ideal but works. Something like:

SELECT s.name, (select a.name, x.score FROM FROM Activity_Score as x
JOIN Activity a ON x.ActivityID = a.ActivityID WHERE x.StudentID = s.StudentID FOR XML AUTO) Activities FROM Students s ORDER BY s.name