0

I critically need help right now as my project cannot be compledted because of this issue. This is my problem:

I have a table "survey". Each survey has 47 questions which users will answer and store into database. i have mistakenly store each 47 questions into 47 rows. Which means that if 5 people take the survey, i will have total number of 47*5(rows) in my table. so imagine i have 10,000 surveys i will then have 47*10,000(rows). This has affected the performance of my application and report.

Now want i want to achieve now is to transpose each 47rows into 47(columns). FOr example 5*47(rows) will give me 5(rows) 47(columns)

UserID | Q1     | Q2     | Q3    | Q4     |--- |Q47
-----------------------------------------------------
user 1 | Answer1 | answer2 |answer3 |answer 4 | ----answer47
user 2 | Answer1 | answer2 |answer3 |answer 4 | ----answer47
user 3 | Answer1 | answer2 |answer3 |answer 4 | ----answer47

This is my code. The code only transpose the data and groupped by userID, but i want to display all the multiple userID with the corresponding answers. PLEASE HELP ME

 $sql = "SELECT agentId, 
                    MAX(IF(questionid= 1, answer, '')) Q1, 
                    MAX(IF(questionid= 2, answer, '')) Q2, 
                    MAX(IF(questionid= 3, answer, '')) Q3, 
                    MAX(IF(questionid= 4, answer, '')) Q4, 
                    MAX(IF(questionid= 43, answer, '')) Q43,
                    MAX(IF(questionid= 44, answer, '')) Q44,
                    MAX(IF(questionid= 35, answer, '')) Q45,
                    MAX(IF(questionid= 46, answer, '')) Q46,
                    MAX(IF(questionid= 47, answer, '')) Q47,
                    FROM surveyanswers users GROUP BY agentId ORDER BY agentId";
codeDev
  • 1
  • 1
  • Can you explain what is the result you expect? – sagi Mar 09 '16 at 10:47
  • http://stackoverflow.com/questions/20111418/sql-server-transpose-rows-to-columns Answer to this question might be helpful to solve your problem. – UserX Mar 09 '16 at 10:48
  • @sagi, This is how i want my table result to look like UserID | Q1 | Q2 | Q3 | Q4 |--- | Q47 ----------------------------------------------------- user 1 | Answer1 | answer2 |answer3 |answer 4 | ---- to answer47 user 2 | Answer1 | answer2 |answer3 |answer 4 | ---- to answer47 user 3 | Answer1 | answer2 |answer3 |answer 4 | ---- to answer47 A user might have more than 100 survey and another user have over 50 survery. all their answers to the survey questions must be displayed in 47 columns with each userID on each row just as i showed in the table able – codeDev Mar 09 '16 at 12:03
  • @UserX the link does not solve my problem. My data is still grouped. i dont want it grouped by angentId. – codeDev Mar 09 '16 at 17:04

1 Answers1

0

http://www.databasejournal.com/features/mssql/converting-rows-to-columns-pivot-and-columns-to-rows-unpivot-in-sql-server.html Your problem is something like this, instead of grouping countries you can group the users.

UserX
  • 119
  • 3