Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ian.tremblay
Starting Member
1 Post |
 Posted - 2011-08-12 : 14:24:42
|
| I need some advise. Currently I have a source file where each row represents a questionnaire, and there are 50 questions where the response could be either 0 or 1. The number of columns will grow as time goes onID|Q1|Q2|Q3|Q4|Q5|Q6|Q7|...|Q501 |0 |1 |0 |1 |0 |0 |0 |...|12 |0 |0 |0 |0 |0 |1 |1 |...|03 |0 |1 |0 |1 |0 |0 |0 |...|1I would like to only store the records where the question response was 1, in a table with 3 columns (ID, Question, Value)ID|Question|Value1|Q2|11|Q4|11|Q50|12|Q6|12|Q7|13|Q2|13|Q4|13|Q50|1Does any one have any suggestions on how this might be accomplished. Thanks! Ian |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-08-12 : 14:31:33
|
| Unpivot:create table #q(ID int not null, Q1 bit not null DEFAULT(0),Q2 bit not null DEFAULT(0),Q3 bit not null DEFAULT(0),Q4 bit not null DEFAULT(0),Q5 bit not null DEFAULT(0),Q6 bit not null DEFAULT(0),Q7 bit not null DEFAULT(0),Q8 bit not null DEFAULT(0),Q9 bit not null DEFAULT(0),Q10 bit not null DEFAULT(0))insert #q(ID,Q1) values(1,1)insert #q(ID,Q4) values(2,1)insert #q(ID,Q7) values(3,1)insert #q(ID,Q3) values(4,1)select * from #qunpivot(value FOR Question IN(Q1,Q2,Q3,Q4,Q5,Q6,Q7,Q8,Q9,Q10)) b |
 |
|
|
|
|
|
|
|
Normalize Data