Forum Discussion
Unpivot Suvey Data from SharePoint List
I have a SharePoint list that has containing survey data - columns like person filling in the survey (Microsoft Form), the date and details. I have a column for each question answer and question comment e.g.
| Name | Date | Question 1 Answer | Question 1 Comments | Question 2 Answer | Question 2 Comments | Question 3 Answer | Question 3 Comments | Question 4 Answer | Question 4 Comments |
| Mickey Mouse | 04/10/23 | Yes | example comment goes here | 100 | This area could be better | 10 | n/a | Yes | this is brilliant |
| Donald Duck | 25/12/2022 | No | 50 | sometimes | 1 | room for improvement | No | this could be better |
How can I unpivot this so that I get a line per question, with an answer and comment column?
| Name | Date | Question | Answer | Comment |
| Mickey Mouse | 04/10/2023 | Question 1 | Yes | example comment goes here |
| Mickey Mouse | 04/10/2023 | Question 2 | 100 | This area could be better |
| Mickey Mouse | 04/10/2023 | Question 3 | 10 | n/a |
| Mickey Mouse | 04/10/2023 | Question 4 | Yes | this is brilliant |
| Donald Duck | 25/12/2022 | Question 1 | No | |
| Donald Duck | 25/12/2022 | Question 2 | 50 | sometimes |
| Donald Duck | 25/12/2022 | Question 3 | 1 | room for improvement |
| Donald Duck | 25/12/2022 | Question 4 | No | this could be better |
5 Replies
I've amended my original message to make things clearer. I currently have one row per survey response, but I want to have a row per question of the survey response, with a question, answer and comment column for each. I am using the unpivot function for just question and answer at the moment, but there is a new requirement to add comment to each line, so I am trying to amend the logic.
Okey, Steve_AAA I have it clearer now.
Then you need to include previous steps to the ones I have told you before.
1) Select Name and date and unpivot other columns
2) You will get two columns called Attribute and Value
3) Split Attribute by delimeter (space) but the most on the right
4) Rename your columns to Question, Type and Value
5) Then, Pivot column as I told you before.
If you have some doubt, please write me back
Hold control and select the first two columns, right click, then "unpivot other columns". Then split the "Attribute" column on something that will split that last word into its own column (the answer/comment word) - I split on number of characters, since the position is always in the same place in the example.
That will put the question number in one column and Answer/Comment label in another. Then Pivot that Answer/Comment column again, choosing "do not aggregate" under advanced and the values column for the values dropdown. That will make Answer/Comment into separate columns and pivot the values under them:
