Dynamic SQL Pivot with no Aggregate

Imagine a Survey database. The layout of the database is meant to be reusable, DRY-conforming and fast. Meaning it's not very user-friendly. A data dump would be a PITA for any user to interpret.

A database diagram is below.

Survey

One thing that I could not find out how to do online was how to write a PIVOT query without using an aggregate function. The reason is that you cannot. You have to aggregate the data. So the trick is to write your query with only one column of results being pivoted -- making the aggregate meaningless.

Another issue I have with PIVOTs is that you need to know something about your DataSet in order to pivot it. That is lame. So the following TSQL query will get a list of all users, their answers to the survey & the questions as columns. It does this by building the PIVOT statement in SQL and then executing it.



/*****************************************
*
* WARNING
* If you receive the following error: 
* Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '('.
* THEN your database is NOT configured with SQL Server 2005 
* Compatibility-Level and PIVOT is not recognized
* 
******************************************/

if object_id('tempdb..#pivotdata') is not null
begin
 drop table #pivotdata
end

declare @questions table( id int identity(1,1), question varchar(500) )
declare @i int
declare @cmd varchar(8000)

select
u.Reference, u.Email, FirstName, Question, Answer
into #pivotdata
from surveyresult r
join surveyuser u on u.[id] = r.surveyuserid
join surveyanswer a on a.[id] = r.answerid 
join surveyquestion q on q.[id] = a.questionid 
order by FirstName


insert into @questions
select distinct question from #pivotdata

set @i = 1
set @cmd = '
   select *
   from #pivotdata
   pivot ( MAX(answer) FOR question IN 
   ('

while @i < (select max(id) from @questions) + 1
begin
 set @cmd = @cmd + '[' + (select question from @questions where id = @i) + '],'
 set @i = @i + 1
end
set @cmd = substring(@cmd, 0, len(@cmd)) + ') ) as p order by email'

execute(@cmd)

if object_id('tempdb..#pivotdata') is not null
begin
 drop table #pivotdata
end


Keep in mind that in order for execute to read from your temp table it must be an on-disk temporary table. No in-memory tables (like @questions) will work.

Comments

Post a Comment

Popular posts from this blog

Fluent NHibernate - Incorrect syntax near the keyword 'Group'

I have to share this. I spent a good amount of time searching around Fluent NHibernate for a way to escape columns in the generated SQL while still using AutoMapping. My initial configuration looked like: Fluently.Configure() .Database(MsSqlConfiguration.MsSql2005.ConnectionString( c => c.Is(ConfigurationManager.ConnectionStrings["Something"].ConnectionString))) .Mappings(x => x.AutoMappings.Add(AutoPersistenceModel.MapEntitiesFromAssemblyOf () .WithSetup(convention => { convention.FindIdentity = p => p.Name == p.DeclaringType.Name + "Id"; convention.GetComponentColumnPrefix = type => type.Name + "Id"; } ) .BuildSessionFactory() This works great, but I had a property in my class (SomeType) that is called Group. This was causing the SqlException: Incorrect syntax near the keyword 'Group' (others would be like: Incorrect syntax near the keyword 'User' or Incorrect syntax near the keyword ...
Read more

Fluent NHibernate + Encrypting Values

I recently created two CLR User Defined Functions for SQL Server 2005 (written in C#) to decrypt & encrypt a given string (based on a hard-coded key). select dbo.EncryptString('Testing') -- Returns: Fjfds243qcm43fsdj2343== select dbo.DecryptString('Fjfds243qcm43fsdj2343==') -- Returns: Testing Pretty simple stuff. This will allow the user with enough access to pull encrypted data out of the database and provide the ability to restrict decryption to other SQL users. Now to implement this in code... Because it's awesome, Fluent NHibernate has a method called FormulasIs that assists in building out values using SQL. public class User { public virtual int Id { get; set; } public virtual DateTime DateOfBirth { get; set; } } public sealed class UserMap : ClassMap { public UserMap() { WithTable("dbo.[User]"); Id(x => x.Id, "[ID]"); Map(x => x.DateOfBirth, "DOB").FormulaIs("dbo.DecryptString(DOB)"); ...
Read more

MapReduce in C# using Task Parallel Library

Back in August I starting playing with a C# implementation of Google's MapReduce algorithm. The implementation was based on something Stephan Brenner did, although I completely refactored it. Today I added a little bit of logic to split up the actual execution of Map & Reduce in this implementation using the Task Parallel Library in .NET 4.0. Check out the source code for MapReduce in C# on GitHub. Below is an excerpt from the Tests on how to implement the library. Counting Words in Files public static List<KeyValuePair<string, int>> Map(FileInfo document, string text) { var items = text.Split('\n', ' ', '.', ',','\r'); return items.Select(item => new KeyValuePair<string, int>(item, 1)).ToList(); } public static List<int> Reduce(string word, List<int> wordCounts) { if (wordCounts == null) return null; var result = new List<int> { 0 }; foreach (var value in wordC...
Read more