Suppose I have a set of employees (which can grow or shrink) and a set of job types I can send any of my employees on (which can grow or shrink) and I want to keep track of the number of complaints I receive for every employee for each job type they've been sent on.
Suppose I decide upon the following schema that has a table for Employees, a table for Job types, and an many-to-many relationship table for the complaints:
PRAGMA foreign_keys = ON;
CREATE TABLE Employees (
"Employee id" INT PRIMARY KEY,
"Employee name" TEXT
) WITHOUT ROWID;
CREATE TABLE Jobs (
"Job id" INT PRIMARY KEY,
"Job name" TEXT
) WITHOUT ROWID;
CREATE TABLE Complaints (
"Employee id" INT NOT NULL,
"Job id" INT NOT NULL,
Complaint INT CHECK (Complaint>=0),
PRIMARY KEY ("Employee id","Job id"),
FOREIGN KEY ("Employee id")
REFERENCES Employees("Employee id")
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY ("Job id")
REFERENCES Jobs("Job id")
ON DELETE CASCADE ON UPDATE CASCADE
) WITHOUT ROWID;
And let's say this is the input data:
INSERT INTO Employees VALUES
(1, "Horace"),
(2, "Bindy"),
(3, "Dunbar"),
(4, "McDoug"),
(5, "Tim"),
(6, "Bo");
INSERT INTO Jobs VALUES
(1, "Concrete delivery"),
(2, "Electrical wiring"),
(3, "Gentleman's 'massage'"),
(4, "Foundation laying"),
(5, "Lumber framework");
INSERT INTO Complaints VALUES
(1,1,5),
(1,2,3),
(1,3,6),
(1,4,9),
(1,5,8),
(2,1,0),
(2,2,2),
(2,3,9),
(2,4,0),
(2,5,0),
(3,1,1),
(3,2,6),
(3,3,84),
(3,4,5),
(3,5,10),
(4,1,7),
(4,2,6),
(4,3,3),
(4,4,0),
(4,5,7),
(5,1,1),
(5,2,0),
(5,3,8),
(5,4,4),
(5,5,10),
(6,1,3),
(6,2,4),
(6,3,1),
(6,4,0),
(6,5,3);
How can I turn the narrow table Grades into a wide table VIEW to easily make a report similar to:
Name | Job1 | Job2 | ... | JobM | Total
=============================================
Employee1 | | | | |
---------------------------------------------
Employee2 | | | | |
---------------------------------------------
... | | | | |
---------------------------------------------
EmployeeN | | | | |
=============================================
Total | | | | |
where each cell of the main body contains the number of complaints? The hard part here is that M, the number of Jobs, can change with time as new jobs are inserted into the Jobs table.
I'm using sqlite3.
I've found some very similar questions here on StackOverflow that either use pivot tables for non-sqlite databases or CASE statements for sqlite. For example,
How to pivot in SQLite or i.e. select in wide format a table stored in long format?
but in those questions the number of columns in the wide-table was finite and could be hard-coded into the view. In this case it depends on the data stored in the Complaints table.
