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.
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 AgreeThe 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 underscoresSo 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, Value1statement2, response2, Value2statementN, 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 splitTo calculate an aggregate response using GROUP BY we need a result set like:SELECT UserID, statement, response, valueI'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 tableselect ...from ...join (select ... from yourfunction) fon ... = 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 KizerSQL Server MVP since 2007http://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 yourfunctionThe problem with that is yourfunction has two parameters: delimiter, and the delimited stringLet me take a stab at it:SELECT I.UserID, F.statement, F.response, F.valueFROM dbo.INTERACTIONS ICROSS JOIN (SELECT statement, response, value FROM dbo.fnLikert('___', I.SurveyResponse) ) F -- fnLIkert returns a table of statement, response, valueI 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 |
|
|
|
|
|
|
|