2009-04-04

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.

No comments: