I would like to have a sql view that shows the survey response data as a tall table instead of a wide table. The view would return columns of sid, qid, response id, and answer. I could then filter this view to find the answers to a specific question across responses.
I have a way to do this, but it is a bit of work. I basically create a sql view that does an UNPIVOT on a survey response table after first making another sql view that converts to nvarchar(max) all the columns in a lime_survey_xxxx response table since the unpivot operation requires all columns to be of the same datatype.
The problem with this approach is it requires creating several sql views that are specific to each survey. I have a sql function that creates a text version of the info in a survey response, and that function needs to specify the survey-specific sql view in order for it to work. Functions don't allow specifying a sql table/view to operate against (no side-effects allowed), so the view/table name has to be hard coded into my function.
I would like a general solution that would allow me to filter a single sql view by sid to get the answer to any question from any survey.
Has anyone figured out a way to do this? I expect the only good answer is to set up some separate job that saves survey responses into a new tall table in addition to the table where the responses are currently saved. A sql trigger could do this, or the end-url from a survey to be used to trigger this save, but this is rather kludgy.
The administrator has disabled public write access.