use CASE with SQLITE for matching fields

Question

I have an SQLite database with student names. Each student has different classes. I have a table with student grades and classes and another table that defines class by type. I'd like to run a query (that I'll be saving as a CSV to use in OpenOffice as an Excel document for a mail merge). I need to list the student's Math grade, History grade, Science grade, and English grade. However, some students have more than 1 math, science, English, or history class. (How) could I create a CASE statement (and possibly a view) that if indeed there is more than one Math class that it can be listed in a view as Math_Class_2 for Example?

Answer 1

The idea is to make SQL do the work, and not do it for it.

Assuming three tables:

Student (StudentNo, StudentName)

Class (ClassNo, ClassName)

Result (ClassNo, StudentNo, Grade)

The query will be:

SELECT StudentName, group_concat(ClassName || "," || Grade)
FROM Student
INNER JOIN Result ON Result.StudentNo=Student.StudentNo
INNER JOIN Class ON Result.ClassNo=Class.ClassNo
GROUP BY Student.Studentno;

(group_concat is another late addition to SQlite. Present in 3.7).

Here's a dump of all the commands I used:

CREATE TABLE [Student] (
[StudentNo] INTEGER  NOT NULL PRIMARY KEY,
[StudentName] TEXT  NULL
);
INSERT INTO "Student" VALUES(1,'Jimmy');
INSERT INTO "Student" VALUES(2,'Bob');
INSERT INTO "Student" VALUES(3,'Anna');
CREATE TABLE [Class] (
[ClassNo] INTEGER  NOT NULL PRIMARY KEY,
[ClassName] TEXT  NULL
);
INSERT INTO "Class" VALUES(1,'English');
INSERT INTO "Class" VALUES(2,'Algebra');
INSERT INTO "Class" VALUES(3,'Geometry');
INSERT INTO "Class" VALUES(4,'Pre-Cal');
CREATE TABLE [Result] (
[StudentNo] INTEGER  NOT NULL,
[ClassNo] INTEGER  NOT NULL,
[Grade] INTEGER  NOT NULL,
PRIMARY KEY ([StudentNo],[ClassNo])
);
INSERT INTO "Result" VALUES(1,1,70);
INSERT INTO "Result" VALUES(1,2,75);
INSERT INTO "Result" VALUES(1,3,80);
INSERT INTO "Result" VALUES(2,1,85);
INSERT INTO "Result" VALUES(2,2,90);
INSERT INTO "Result" VALUES(3,4,95);

Running the query returns this (you might want to adjust your separating character):

Jimmy,English,70,Algebra,75,Geometry,80

Bob,English,85,Algebra,90

Anna,Pre-Cal,95

Answer 2

My two cents...

I am currently working on an android app that uses a sqlite database. I needed to use a CASE statement and it worked!!!.

The table has a column with a time frequency value in milliseconds, but for the end user I needed to show the information in a easier way to understand, so this is the sql statement that I used (by the way, this statement is put together programatically... it is not hardcoded) :-p

SELECT 
case when alarm_time_frequency='0' then 'Not Used' 
when alarm_time_frequency='300000' then '5 min' 
when alarm_time_frequency='600000' then '10 min' 
when alarm_time_frequency='900000' then '15 min' 
when alarm_time_frequency='1800000' then '30 min' 
when alarm_time_frequency='3600000' then '1 hour' 
when alarm_time_frequency='7200000' then '2 hours' 
when alarm_time_frequency='10800000' then '3 hours' 
when alarm_time_frequency='14400000' then '4 hours' 
when alarm_time_frequency='21600000' then '6 hours' 
when alarm_time_frequency='28800000' then '8 hours' 
when alarm_time_frequency='43200000' then '12 hours' 
when alarm_time_frequency='86400000' then '24 hours' 
end 
as alarm_time_frequency FROM ALARMS 

I hope this helps...