Sample Data download: http://goo.gl/lcOf8z
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