I am building a database to feed into a PowerBI dashboard.

The data is extracted as CSV (SurveyResults) from Survey Monkey which will be loading into the MSSQL database.

The outcome i am after is the ability to provide an Average for each KRA.

The response table has a unique row for each survey response, with each question as a separate column, with a score for a value.

Each Question is assigned a KRA, so i have created the lookup table (Question_KRA).

can someone point me in the right direction in how i can structure the data in order to have a summary table which describes the KRA and the average?

I would like to avoid hard coding in a view, as i would like the ability to add in new questions, with relative ease.

enter image description here

1 Answer 1


You can use UNPIVOT to transpose the question columns from SurveyResults into rows and then average the KRA values from there. This db<>fiddle shows an example.

The first query shows the UNPIVOT and the second query shows a dynamically generated SELECT statement that would allow you to add additional question columns without needing to recode the SELECT statement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.