This gives the same result set you got and does not need to be edited:
select
applicantid,
group_concat(answer, '|')
from
(select applicantid, answer from tblanswers order by questionid)
group by applicantid;
The group_concat() function is part of recent versions of SQLite. It is
returning a single string rather than columns, but it can be parsed. The
subquery ordering by questionid is needed to ensure that the columns
(answers) from all respondents are in the same order.
However, this will only work if tblanswers will always have one record for
every question. That is, if your applicants skip one or more questions,
you will still need to insert a record for skipped questionids, perhaps
with a default answer like 'NOT ANSWERED'. Similarly, if you add new
questions to tblquestions later, you will need to insert 'NO ANSWER'
values into tblanswers for existing applicantids, othewise the columns
(answers) will not align.
Chris
On Tue, 8 Jul 2008, Andrea Connell wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
