I'm using SQL Server 2008, I'm importing the data using the Import Wizard.
I have data from an excel file like the following...
SessionID Email SubmitTime Question1 Question2 Question3
---------- ----- ----------- --------- --------- ----------
54535212 [email protected] 12/21/2011 Yes No Yes
54532342 [email protected] 12/22/2011 Yes Yes Yes
54535288 [email protected] 12/23/2011 No No Yes
54532399 [email protected] 12/24/2011 No Yes Yes
Now I want to store it in my database so I can easily access the data. I'm thinking I should make a Users Table, Questions Table, Answers Table, Submitted Table. For my purposes the Submitted Table can become a column for the User’s Table.
User
`Email`
`Submitted`
`SessionID`
Question
`Question1`
`Question2`
`Question3`
Answer
`Email`
`Question`
`Answer`
How can I populate the Answer table with a query from the Excel document? Or the initial table created from the Excel document on import? This seems like an easy problem but the solution is for some reason eluding me. Thanks for any help!
Your data structure's a bit screwed up. If you have three columns for Question1 through Question3, then you'd want a SurveyID column or something of the sort to identify the unique combinations. Then, your answer table would have UserID, SurveyID, Answer1, Answer2, and Answer3 columns.
If your Question table has one Question column and a QuestionID column (hopefully an identity column so it auto-increments, then you'd do something like this to populate the Answer table from your Import table:
insert into answer (userid, questionid, answer)
select
u.userid,
q.questionid,
case
when q.question = 'Question1' then i.question1
when q.question = 'Question2' then i.question2
when q.question = 'Question3' then i.question3
else null
end as answer
from
import i
inner join users u on
i.email = u.email
cross join question q