Likert Reporting Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Likert Reporting Query

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2014-11-13 : 16:19:36

I've got a table with rows containing likert responses:

Here's an example of a likert: Forums help with my job: Strongly Agree, Agree, Neutral, Agree, Strongly Agree

The user response is stored in an INTERACTION table in columns: UserID, SurveyResponse

The problem is that the column SurveyResponse (VARCHAR) holds several likert statement/responses like so:

statement1___response1___statement2___response2___statementN___responseN -- the delimiter being three underscores

So there's a string split problem there which I solved by writing a function fnLikert which can split the likert string and return a table like so:
statement1, response1, Value1
statement2, response2, Value2
statementN, responseN, Value3 -- Values are 1...5 depending on the textual response.

Now comes the twisty turny part: Report an aggregate summary for Statement(i) for ALL INDIVIDUALS...

SELECT UserID, SurveyResponse -- Returns a recordset of all user responses but the column SurveyResponse isn't yet split

To calculate an aggregate response using GROUP BY we need a result set like:

SELECT UserID, statement, response, value

I've been away from SQL for a long time so this may be easy, or it may be hard. Can the result set be created without looping through the recordset?

Sam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-13 : 16:35:55

You can use a derived table, CTE or a temp table so that you can query the split results.

Derived table
select ...
from ...
join (select ... from yourfunction) f
on ... = f.UserID...

It's going to be slow. A design change will be needed if performance matters, ie don't store the data like that.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

SamC
White Water Yakist

3467 Posts

Posted - 2014-11-13 : 16:49:37

It's a 3rd party database, and another 3rd party product that inserts the data. So... it'll be slow.

I am stuck on the example you provided here:

select ... from yourfunction

The problem with that is yourfunction has two parameters: delimiter, and the delimited string

Let me take a stab at it:

SELECT I.UserID, F.statement, F.response, F.value
FROM dbo.INTERACTIONS I
CROSS JOIN (SELECT statement, response, value FROM dbo.fnLikert('___', I.SurveyResponse) ) F -- fnLIkert returns a table of statement, response, value

I have not tried the above because it looks like that function just won't be called for every instance of I.SurveyResponse

SamC
White Water Yakist

3467 Posts

Posted - 2014-11-13 : 17:04:28

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104485

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources