SQL - columns for different categories

user_436193 0
Viewed 6462

I am new to SQL. I have a database with data for different exams, for example:

Student Test Grade
--------------------
St1    T1   A
St2    T1   B
St3    T1   B
St1    T2   B
St2    T2   B
St3    T2   A
St1    T3   A
St2    T3   C
St3    T3   B

Then, I would like to print a report using the Tests (T1, T2 and T3) as columns:

Student  T1   T2   T3
----------------------
St1      A    B    A
St2      B    B    C
St3      B    A    B

I have tried different things, but I got stuck on how to produce such a printout. Any help is appreciated!

pivot sqlite sql
6 Answers

I would like to add some explanation to @OMG_Ponies answer because it could be useful for SQL no-super-users (like myself)

Let's create an example table and add the dummy data:

CREATE TABLE t (
   t_ID integer primary key autoincrement not null,
   student integer,
   test text,
   grade text
);

INSERT INTO t 
   (student, test, grade)
VALUES
   ('St1', 'T1', 'A'),
   ('St2', 'T1', 'B'),
   ('St3', 'T1', 'B'),
   ('St1', 'T2', 'B'),
   ('St2', 'T2', 'B'),
   ('St3', 'T2', 'A'),
   ('St1', 'T3', 'A'),
   ('St2', 'T3', 'C'),
   ('St3', 'T3', 'B');

So we have the following:

t_ID student test grade
-------------------------
 1   St1     T1     A
 2   St2     T1     B
 3   St3     T1     B
 4   St1     T2     B
 5   St2     T2     B
 6   St3     T2     A
 7   St1     T3     A
 8   St2     T3     C
 9   St3     T3     B

Using the statement case when ... then ... end it is possible to get the desired columns

SELECT 
   t_ID,
   student,
   (case when t.test = 'T1' then t.grade end) as T1,
   (case when t.test = 'T2' then t.grade end) as T2,
   (case when t.test = 'T3' then t.grade end) as T3
FROM t
   order by student

Result

t_ID student  T1    T2     T3
----------------------------------
1    St1      A    NULL  NULL       
4    St1     NULL   B    NULL   
7    St1     NULL  NULL   A
2    St2      B    NULL  NULL   
5    St2     NULL   B    NULL   
8    St2     NULL  NULL   C
3    St3      B    NULL  NULL   
6    St3     NULL   A    NULL   
9    St3     NULL  NULL   B

However, we see that it is necessary to group the results by the field "student". When we group we have to specify an aggregate function to specify which value to keep in case of having more than one row with the same value of "student". In this case we use the "max" function, to discard the null's.

SELECT 
   t_ID,
   student,
   max(case when t.test = 'T1' then t.grade end) as T1,
   max(case when t.test = 'T2' then t.grade end) as T2,
   max(case when t.test = 'T3' then t.grade end) as T3
FROM t
GROUP BY student
ORDER BY student

Result

t_ID student  T1   T2   T3
-----------------------------
7    St1      A    B     A      
8    St2      B    B     C  
9    St3      B    A     B

Final note. Since we have not grouped also by t_ID, nor we have specified an aggregate function for it, you should assume that the value of t_ID of each row is a random one of each group. Be careful with that.

user_62815750
Related