Been Busy:
I've been pretty busy of late with things not related to this general thread of gaining an understanding of MVC and all that it entails. One of the things I've been working on has been pulling user response data out of some survey software we're using as part of a clinical study at my real job. The project is to show the efficacy of the Text4Baby Project. The tool we used to collect the data is a modified version of Select Survey .Net (Which I highly recommend) The canned reports didn't output what we needed especially after the modifications we made.Whut They Wanted:
It has a very relational structure in the SelectSurvey database and is a bit difficult to get data out of. One of the things the people doing the study (Real scientists) wanted was that the data be in a structure sort of like this.| RespondentId | Q1Answer | Q2Answer | Q3Answer | QNAnswer... |
| #1 | Yes | No | Some Times | Twice |
| #2 | Yes | Yes | Never | More than 10 |
| #3 | No | No | Never | Got Caught |
It didn't look like that at all in the SelectSurvey database. It turned out to be fairly difficult to get it all worked out. For me anyway.
A bit of background:
I was riding on the shoulders of giants (Mark K. mostly) who had done a bunch of related preliminary work in figuring out the SelectSurvey database for us so it was much easier to do than if I had to do it all on my own. Additionally, many moons ago I'd come up with a way to put these column names into the SelectSurvey application using a field they already had there. It's called 'item_alias' but was intended for a different purpose. When I originally thought up the concept I had contacted the makers of SelectSurvey with my idea for a work around and suggest they add a new field to the application. A year or so later I contacted them to see how it was coming and they quoted back my hack as the suggested solution. kay-sara-sara.The last time I wanted to do a pivot query in SQL Server it wasn't available. I'd gotten the concept from MS Access back when I was doing a lot of work with it. I couldn't understand why it wasn't available in these big database engines when little MS Access had it. Well, that was a long time ago and it could now be done in SQL Server.
Finding a Solution:
When Googling for how to do this in SQL Server. One of the first posts on the subject I found was this one: Pivot tables in SQL Server. A simple sample.: It's a pretty good example of what Pivot queries are used for and getting a handle on the general concept.In the examples the poster was pretty much doing what I needed except I had a lot more column names than days of the week. Typing all of those into a query, and getting them right, seemed a bit daunting. What I wanted was for those column names to be dynamic and pulled out of the records themselves in that 'item_alias' field I mentioned previously.
I found how to do that in the response to this post: SQL Server PIVOT Column Data on stackoverflow.
You'll see in the response by astander that it's broken down into three parts (The first section with the CREATE TABLE is just populating a table with data so I'm not including that in this discussion)
The first part He creates a variable to store the list of columns in the right structure as indicted in the example in the first list. e.g. "[MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN]" by selecting the column names using a query. (Note the COALESCE thingy) From there, he creates a string with the Pivot query in it and substitutes the results from the prior query as the list of columns to get as the results of that Pivot query.
Then it's executed.
Implementation:
About all I did was copy the text of his post and substitute in the names for the tables and columns in the SelectSurvey database.The Details:
If you want them, please ask. It's not that easy to write up so I'm not going to put it all together and post it when my only readers at this point are one close friend and my mother. What you'd get would be queries to create the pivot data using the SelectSurvey database and some setup data. to see it work. It would take a little bit of doing because the queries I'd post would have to be modified versions of the ones I'm using that don't include the table(s) we added for our extension of SelectSurvey.Thanks:
I'd really like to thank the poster of the answer, astande, but I don't have enough points yet on stackoverflow to post a response yet.*I hope to someday post the entirety of the mentioned modifications we made to SelectSurvey. Actually, we didn't make any changes to the application itself. What we did was built a wrapper around it that allowed us to chain surveys. The respondent would take the first survey which would collect their unique ID. The system would then check the database and figure out which survey(s) the respondent was supposed to take next. All of this may be moot as the version of SelectSuvey.NET we have on hand is a few years old and the basic idea wouldn't work with newer versions.
