I'm not sure if what I want is possible to do in SQL, but I -am- sure
that one of you will know.
Given two tables - one with questions to ask applicants and one with an
applicant's answer to a particular question - I want to make a flattened
view with all of an applicant's answers in one row. This is easy enough
to do when I know ahead of time which questions are in the first table,
but I don't like the hard-coded approach.
My schema is as follows:
CREATE TABLE tblquestions (questionid int, question varchar(100));
INSERT INTO "tblquestions" VALUES(1,'whats up');
INSERT INTO "tblquestions" VALUES(2,'how are you');
INSERT INTO "tblquestions" VALUES(3,'whats your name');
CREATE TABLE tblanswers (questionid int, applicantid int, answer
varchar(2500));
INSERT INTO "tblanswers" VALUES(1,100,'stuff for answer one');
INSERT INTO "tblanswers" VALUES(2,100,'stuff for answer two');
INSERT INTO "tblanswers" VALUES(3,100,'stuff for answer three');
INSERT INTO "tblanswers" VALUES(1,200,'random text one');
INSERT INTO "tblanswers" VALUES(2,200,'random text two');
INSERT INTO "tblanswers" VALUES(3,200,'random text three');
Here is the view I have come up with so far, which would require editing
whenever an insert or delete is done on tblQuestions.
CREATE VIEW allanswers as
SELECT applicantid,
(select answer from tblanswers Z where questionid = 1 and
Z.applicantid = A.applicantid) As Answer1,
(select answer from tblanswers Z where questionid = 2 and
Z.applicantid = A.applicantid) As Answer2,
(select answer from tblanswers Z where questionid = 3 and
Z.applicantid = A.applicantid) As Answer3
FROM tblanswers A
group by applicantid;
sqlite> select * from allanswers;
100|stuff for answer one|stuff for answer two|stuff for answer three
200|random text one|random text two|random text three
Has anybody come across a problem like this and found a reasonable
dynamic solution? Even something like a trigger on tblQuestions to
change the View would be great, I'm just not sure about the SQL
involved. If it helps, I don't really care what the columns end up being
named. Also I can't guarantee that the questionids will be consecutive
or in any order.
Thanks,
Andrea
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
