0

I have a database that is something like the following: 

+------------+-----------+------------+
| student_id |  subject  | test score |
+------------+-----------+------------+
|          3 | math      |         78 |
|          3 | physics   |         66 |
|          3 | english   |         98 |
|          2 | math      |         76 |
|          2 | physics   |         57 |
|          2 | geology   |         87 |
|          2 | english   |         99 |
|          1 | math      |         86 |
|          1 | physics   |         85 |
|          1 | astronomy |         89 |
|          1 | history   |         77 |
+------------+-----------+------------+

I would like to group the subject_ids. The labels in the second column would become columns, and the new rows would be the scores. So it should look something like this:

+------------+------+---------+---------+---------+-----------+
| student_id | math | physics | english | geology | astronomy |
| 1          | 86   | 85      |         |         | 89        |
| 2          | 76   | 57      | 99      | 87      |           |
| 3          | 78   | 66      | 98      |         |           |
+------------+------+---------+---------+---------+-----------+
mvp
  • 111,019
  • 13
  • 122
  • 148
user2144412
  • 123
  • 1
  • 9
  • mvp, thanks for the correction, how did you do that? – user2144412 Apr 14 '13 at 05:25
  • welcome to Stackoverflow! You should read more about [Markdown syntax](http://daringfireball.net/projects/markdown/syntax), it will be useful here and on many other sites, like github. Also, be sure to search first before asking - many questions have already been asked before – mvp Apr 14 '13 at 05:30
  • Do you actually want to change the database schema or just want a select statement that will output like the second example? – Dondi Michael Stroma Apr 14 '13 at 07:03
  • Search for `crosstab` and/or `pivot`. – Craig Ringer Apr 14 '13 at 10:52
  • [My go-to answer for crosstab queries.](http://stackoverflow.com/questions/3002499/postgresql-crosstab-query/11751905#11751905) – Erwin Brandstetter Apr 14 '13 at 13:41
  • Thx for the info, the link above (http://stackoverflow.com/questions/2099198/sql-transpose-rows-as-columns) is exactly what I was looking for. I did find it in a search before I posted my question but saw his 2 tables that he wanted combined and thought those answers didn't apply, I didn't realize that the 2nd table was extraneous. I did just want a SELECT statement, not to change the underlying structure. After looking at the "markdown syntax" link I'm still not clear what to use for a SQL-like table. I tried several of the formatting options to try to get my ascii to display properly. – user2144412 Apr 14 '13 at 20:04

0 Answers0