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?

What are your uniqueness constraints? May a single student take part in more than one activity? May a single student take part in the same activity more than once? May the single student take place in the same activity more than once and receive the same score more than once? — John Saunders, Jun 14 '09 at 01:52
Let's say each student will only have one score for one activity, and ought to have exactly one score for each activity. — , Jun 14 '09 at 02:05

score 3 · Answer 1 · edited May 23 '17 at 12:25

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

score 2 · Answer 2 · answered Jun 14 '09 at 02:10

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.

score 1 · Answer 3 · edited Oct 28 '11 at 19:06

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') football_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.

you probably want to name your second subquery result football_score. — akf, Jun 14 '09 at 02:16

score 1 · Answer 4 · edited May 23 '17 at 12:17

This result set shape offends relational algebra (on which SQL is based). See SQL antipattern post #2 What are the most common SQL anti-patterns?

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

score 1 · Answer 5 · edited May 23 '17 at 11:46

1

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.

edited May 23 '17 at 11:46

Community

1
1

answered Jun 14 '09 at 03:03

Cade Roux

88,164
40
182
265

+1 "ANSI SQL is that the result set is not well-defined" The phrase I've been looking for... – gbn Jun 14 '09 at 07:08

score 0 · Answer 6 · edited Jul 25 '11 at 03:29

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

SQL select: two-dimensional select with variable column count

6 Answers6

Linked