Dynamically create columns in a temp table and generate pivot report

Sample Data download: http://goo.gl/lcOf8z

Sample survey submitted data

Generate excel report as below.

Username, Address, City, State, Zip, Question 1, Question 2, Question 3, …..
Prakash, test, anand, gujarat, 388001, Answer 1, Answer 2, Answer 3,..
Chirag, test, anand, gujarat, 388001, Answer 1, Answer 2, Answer 3,..


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Prakash Rathod
-- Create date: 18-Mar-2014
-- Description:	Generate dynamic pivot report of submited survey
-- Exec spSubmitedSurveyExcelReport 'FCDC5B31-58D6-4C25-92ED-74665E343141'
-- =============================================
ALTER PROCEDURE spSubmitedSurveyExcelReport
	@SurveyID uniqueidentifier
AS
BEGIN	
	SET NOCOUNT ON;
	
	create table #distictCaption (caption nvarchar(1000)) -- create temporary table for storing questions.

	insert into #distictCaption
	Select distinct caption From submitedsurvey s join users u on u.id = s.userid where surveyid=@SurveyID

	Create table #tempSurveyAnswer (userid uniqueidentifier, Name varchar(200), [Address] varchar(500), city varchar(200) , [state] varchar(200), zip varchar(10), dummy bit) -- create temp table for creating dynamic columns, questions become columns

	Declare @Script as Varchar(8000);
	Declare @Script_prepare as Varchar(8000);
	 
	Set @Script_prepare = 'Alter table #tempSurveyAnswer Add [?] varchar(100);'
	Set @Script = ''

	Select
				@Script = @Script + Replace(@Script_prepare, '?', caption)
	From #distictCaption 

	Exec (@Script)
	 
	Alter table #tempSurveyAnswer drop column dummy;

	insert into #tempSurveyAnswer(userid ,Name, [Address], City, [State], Zip) 
	select s.userid, u.firstname, u.[Address], u.City, u.[State], u.Zip from submitedsurvey s
	join users u on u.id = s.userid
	where surveyid=@SurveyID group by s.userid, u.firstname, u.[Address], u.City, u.[State], u.Zip
	 
-- update temp table
	DECLARE @sql NVARCHAR(MAX)
	SET @sql = ''

	select @sql = @sql + ' UPDATE #tempSurveyAnswer ' + CHAR(13) +
	' SET [' + c.name + '] = (SELECT value ' + CHAR(13) +
	' FROM submitedsurvey' + CHAR(13) +
	' where surveyid=@I and #tempSurveyAnswer.userid = submitedsurvey.userid and submitedsurvey.caption = ''' + c.name + ''') '+ CHAR(13) 
	from tempdb.sys.columns c
	where object_id = object_id('tempdb..#tempSurveyAnswer') and name'userid' and name'Name' and name  'Address' and name'city' and name'state' and
	name'zip';

	print(@sql)

	exec sp_executesql @sql,N'@I uniqueidentifier',@I=@SurveyID;

	Select * from #tempSurveyAnswer;

	drop table #distictCaption
	drop table #tempSurveyAnswer
END
GO


Share