CODEWITHSUNDEEP
sql
p.developer
p.developer

Reputation: 57

Select statement to turn table columns into rows

I have the following SQL table called 'tblQuestions':

    questionID    Question      Option1 Option2 Option3 Option4 
      1          Whats 1 + 1         1     2       3      4

I am trying to do a select statement for the options, which will convert the row into the following format:

 AnswerText AnswerValue
    1          1    
    2          2
    3          3
    4          4

So to have each option instead as an individual column, but as separate rows instead

Apologies if table aren't in the correct format and thanks for the help!

Upvotes: 1

Views: 57

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

I would strongly suggest restructuring your database to something like the following:

tblQuestions

PK = Question_ID

+-------------+--------------+
| Question_ID |   Question   |
+-------------+--------------+
|           1 | What is 1+1? |
|           2 | What is 2+2? |
|         ... | ...          |
+-------------+--------------+

tblOptions

PK = Question_ID + Option_ID

+-------------+-----------+--------------+
| Question_ID | Option_ID | Option_Value |
+-------------+-----------+--------------+
|           1 |         1 |            1 |
|           1 |         2 |            2 |
|           1 |         3 |            3 |
|           1 |         4 |            4 |
|           2 |         1 |            7 |
|           2 |         2 |            5 |
|           2 |         3 |            4 |
|         ... |       ... |          ... |
+-------------+-----------+--------------+

Then perhaps an answers table containing the correct options for each question -

tblAnswers

PK = Question_ID

+-------------+-----------+
| Question_ID | Option_ID |
+-------------+-----------+
|           1 |         2 |
|           2 |         3 |
|         ... |       ... |
+-------------+-----------+

Upvotes: 4

Related Questions