Here at Winona State we have invested significant time and energy in campus-wide assessment efforts for many years. Online surveys are the backbone of those efforts, and Qualtrics (www.qualtrics.com) is our tool of choice for conducting surveys. One strength of the system is the reporting tool set available, and one unobtrusive Qualtrics feature that we have found very useful in combination with that tool set is the ability to import survey responses from a text file straight into a Qualtrics survey. If that doesn’t sound tremendously helpful at first blush, just bear with me.
Imagine being the administrator of two different surveys, both delivered to university sophomores, one in fall, and one in spring. Responses to both surveys have been collected, and the surveys have closed. At that late stage, you realize that it would be very helpful to explore the correlations between a question on Survey A and several questions on Survey B. It would also be helpful to include the college affiliation (e.g., College of Business versus College of Liberal Arts) of the respondents in your reporting. In our office, we frequently find ourselves in similar situations. Obviously, there are many solutions to the problem, but Qualtrics provides a good one.
It is relatively straightforward in Qualtrics to approach the problem this way:
I will follow this post with an example of how we’ve used this technique for quick, ad hoc reporting that brings together data from multiple surveys with SIS information using only duct tape and common household items. Stay tuned.
For those of us who spend time querying Microsoft SQL Server, each new version of the product gives us new functionality that can be as helpful as a new belt full of tools to a carpenter. One tool (first available in SQL 2008) that has proved useful in our ad hoc queries is the expanded VALUES clause. I use it in three primary ways:
Some SQL statements that spell out the details of those three techniques follow. The first two are fairly easy to understand by simply running the SQL code. The third technique is a bit more involved: for more explanation and code samples, refer to this article in SQL Server Magazine by Itzik Ben-Gan.
-- declare a table variable for use below declare @TableVar table ( TermId int, LicenseId int, MaleCount int, FemaleCount int, [Description] varchar(500) ) -- first use of the values clause: one insert statement inserting multiple rows. insert into @TableVar values (20083, 1, 12, 15, 'Sample data - bogus'), (20093, 4, 31, 47, 'Sample data - bogus'), (20103, 3, 50, 22, 'Sample data - bogus'), (20113, 2, 67, 83, 'Sample data - bogus'), (20123, 4, 39, 58, 'Sample data - bogus') -- second use of values clause: inline, quick & dirty derived table select t.TermId, t.LicenseId, t.MaleCount, t.FemaleCount, tmp.* from ( values -- use values to create derived table (1, '130301-N','Science Chemistry 9-12 + 5-8 General Science'), (2, '130302-N', 'Science Physics 9-12 + 5-8 General Science'), (3, '130400-N', 'Science Earth 9-12 + 5-8 General Science'), (4, '130600', 'Science General 5-8') ) as tmp (LicenseId, LicenseCode, LicenseDescription) -- name columns join @TableVar t on tmp.LicenseId = t.LicenseId -- third use of values clause: aggregate function over columns -- rather than rows. Better performance than unpivot with large sets. select TermId, ( select max(mycol) from (values ('FemaleCount', FemaleCount), ('MaleCount', MaleCount)) as mytable(mylabel, mycol) ) maximum_value from @TableVar -- compare syntax above to case statement select TermId, case when FemaleCount > MaleCount then FemaleCount else MaleCount end maximum_value from @TableVar -- now compare to unpivot select TermId, max(col) as maximum_value from ( select TermId, col from @TableVar unpivot (col for cols in (FemaleCount, MaleCount)) as unpivott ) p group by TermId