February 5, 2006 at 4:04 pm
Hi Everyone,
Let me first preface my question with I am Novell Engineer that has be retasked to write Web Applications and administer the Server (w2k3) and the databases on it (MS-SQL 2000) So I don;t have a DB pedigree to begin with...
I have a table that records the answers of a questionaire. something like;
user_id, answer1, answer2, answer3, answer4
I have been asked to create a report that is respondant centric. so the above needs to change to;
user_id, answer1
user_id, andwer2
user_id, answer3
user_id, answer4
I PM'd Steve Jones for some direction. He provded me with the code below but also suggested that I post in the forums to see if anyone else has any other ideas. Also, let me re-iterate to you what I said to Steve, I am not necessarily after the Answer (that would be great!) but I am more than happy to "nut it out" for myself. So a " go and check out ....." would be just as good.
select a.question
, a.answer1
, b.answer2
, c.answer3
from questions a
inner join questions b
on a.question = b.question
inner join questions c
on a.question = c.question
Thanks in advance - all help appreciated.
Gavin Baumanis
Smith and Wesson. The original point and click device.
February 5, 2006 at 7:46 pm
select user_id, answer1 from yourtable where ...
union all
select user_id, answer2 from yourtable where ...
union all
select user_id, answer3 from yourtable where ...
union all
select user_id, answer4 from yourtable where ...
February 5, 2006 at 8:32 pm
Thanks for the reply....
Looks like it will definately work...
The only half problem I have with your approach is that in the current questionnaire there is something in the order of 87 questions.
Thats a lot of typing.
And when the next (totally different) questionnaire is created/required I will have to redo the T-SQL again to match the new number of questions. Not a real problem if I am going through the process of typing up 87 questins worth this time around...
But for arguments sake, if the next questionnaire is 150 questions - then I'll need to add in a further 70 answers worth of T-SQL processing.
I haven't got a "sexy" answer myself... and it looks like I will be doing a lot of typing!!! but I live in hope for a "nice/elegant" way!
Gavin Baumanis
Smith and Wesson. The original point and click device.
February 6, 2006 at 12:45 am
You don't have to do that much typing... In your favourite programming language, write a loop with 87 (or 150) iterations to produce the query you are after. Then copy and paste it into query analyzer or your T-SQL code or whereever you want to use it.
You may even write T-SQL code that builds this query and executes it (with exec) in the same program. Have a look at http://www.sommarskog.se/dynamic_sql.html
February 7, 2006 at 6:57 am
Do you have any control over the schema? That is, can you change it or redesign it so that you've got multiple tables instead of everything glommed into a single table? It would make your life a lot easier now (and going forward, when you have additional questionnaires and questions) to redesign this thing. The current design, in a single table, doesn't seem all that amenable to having a large number of questions or additional questionnaires.
If it's not in your power to redesign the original solution, then maybe you could "preprocess" the source table into two tables (one of users, one of answers keyed by user) and then simply join the two. Seems like it'd be pretty straightforward to do that in a stored proc.
February 7, 2006 at 7:06 am
alternatively is use dynamic sql to do this
February 7, 2006 at 8:51 am
Hello,
Is not very difficult with dynamic sql and WHILE. Try:
-- Your table
CREATE TABLE TEST_COL
(Id smallint,
col1 smallint,
col2 smallint,
col3 smallint,
col4 smallint,
col5 smallint)
INSERT TEST_COL
SELECT 1, 11, 21, 31, 41, 51 UNION
SELECT 2, 12, 22, 32, 42, 52 UNION
SELECT 3, 13, 23, 33, 43, 53 UNION
SELECT 4, 14, 24, 34, 44, 54 UNION
SELECT 5, 15, 25, 35, 45, 55
-- New table for OUTPUT
CREATE TABLE TEST_OUT
(Id smallint,
col smallint)
-- Procedure
DECLARE @table varchar(100), @col varchar(100)
SELECT @table = 'TEST_COL'
DECLARE @pos smallint, @max-2 smallint
SELECT @pos = 2 -- skip the Id
SELECT @max-2 = (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table)
WHILE @pos <= @max-2
BEGIN
SELECT @col = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table AND ORDINAL_POSITION = @pos)
INSERT TEST_OUT
EXEC ('SELECT Id, ' + @COL + ' FROM ' + @table )
SELECT @pos = @pos + 1
END
SELECT * FROM TEST_OUT
Liliana.
February 7, 2006 at 2:43 pm
A big thanks to everyone for their reply.
I really appreciate it.
I ended up programming a loop in T-SQL as suggested and it works quite nicely.
With regards to having access to the schema and changing the tables... I do have that access (I am the server / database admin and webmaster- no real DB skills mind you - short of being able to construct SQL to put data in a table and get it out again)
but I don't see how I will change the tables to make things easier to mange.
I do have a user table and an answers table.
The answers table has a an Id column, user_id column, and a column for each answer.- in this case 87 answer columns.
(first off - I am more than happy to be told of a better way to do things - that's why I am here asking questions) But I don't see a way to redesign the DB schema that would enable a report to be generated any easier so that the questionnaire reviewer can have all of user 1's answers grouped, then user 2's etc.
The loop is working to do this now, but if the real answer is to redesign the schema and avoid the problem in the first place... then that would seem to be a better long-term solution and I am certainly open to any suggestions/advice that anyone might have.
Gavin Baumanis
Smith and Wesson. The original point and click device.
February 9, 2006 at 1:07 am
You could redesign the answers table such that the columns are as follows:
Id int, user_id int, answer_id int, answer varchar(100)
Sample data would be as follows:
1, 1, 1, 'User 1's answer to question 1 on questionaire 1'
1, 1, 2, 'User 1's answer to question 2 on questionaire 1'
1, 1, 3, 'User 1's answer to question 3 on questionaire 1'
1, 7, 1, 'User 7's answer to question 1 on questionaire 1'
1, 7, 2, 'User 7's answer to question 2 on questionaire 1'
1, 7, 3, 'User 7's answer to question 3 on questionaire 1'
To get all user_id 1's answers to all questions on questionaire 1, do as follows:
select answer from answers where id = 1 and user_id = 1 order by answer_id
To get answers for all users to all questions on questionaire 1, try this:
select user_id, answer from answers where id = 1 order by user_id, answer_id
The advantage is that you can have all questionaires in the same table (you might need a questionaire table as well). On the other hand, you will have to change your web client code, as data is on a different form when it is returned from the database. But I am sure you won't regret making this change - it pays off in the long run.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply