Thanks Chris & Dennis for the group_concat tip... It is an interesting idea but I don't think it makes sense to use this time. If I have to parse the results in my code, I might as well just get the answers in separate rows and group them together in code without having to worry about returning values for the non-answered questions.
I knew this wouldn't be an easy thing to solve, but appreciate the input. I am still holding a shred of hope for a trigger that can recreate the view whenever the questions table is modified but I haven't put much thought into it yet and I'm sure it's just as challenging. I'm going to keep working on this and see if I can come up with anything. If anybody thinks of something, let me know. I know that I could do this in code, and at this point it would probably take less time, but I'd really like to see if this is possible more than anything. Thanks -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, July 08, 2008 1:21 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] View with Dynamic Fields ? 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
