Below is code that Chad first offered that lists Dynamic Forms captured data as a common table. I modified it for my purposes. Notice the use of "CHARINDEX(' ',@Question)." I used it to find and eliminate unwanted characters. You can set up a cursor and loop through looking for the space character assuming that for every space you find you have found a word. Count the spaces and add one at the end for the last word.
Hope this helps.
-- sp_DynamicForms_ExportFormResultsInTempTable
-- to run: sp_DynamicForms_ExportFormResultsInTempTable 466, 'Y'
--------------------------------------------------------------------------
-- IF AN OLDER VERSION OF THIS STORED PROCEDURE IS LOADED, THEN UNLOAD IT
----------------------------------------------------------------------------
if exists (select * from sysobjects
where id = object_id('dbo.sp_DynamicForms_ExportFormResultsInTempTable') and sysstat & 0xf = 4)
drop PROCEDURE dbo.sp_DynamicForms_ExportFormResultsInTempTable
GO
----------------------------------------------------------------------------
-- LOAD THIS STORED PROCEDURE
----------------------------------------------------------------------------
Create Procedure sp_DynamicForms_ExportFormResultsInTempTable
@ModuleID int = null,
@PrintSQLToExecute varchar(1) = null
AS
----------------------------------------------------------------------------
-- TURN OFF ROW COUNT DISPLAY & WARNING MESSAGES
----------------------------------------------------------------------------
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
----------------------------------------------------------------------------
-- DROP TEMPORARY TABLES LEFT OVER FROM PREVIOUS IMTERRUPTED RUNS
----------------------------------------------------------------------------
--if (SELECT object_id('tempdb..tblExportFormResults')) IS NOT NULL
-- drop table tblExportFormResults
if exists (select * from sysobjects where id = object_id(N'dbo.tblExportFormResults') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.tblExportFormResults
----------------------------------------------------------------------------
-- DEFINE TEMPORARY VARIABLES
----------------------------------------------------------------------------
Declare @Question VarChar(500);
Declare @DynamicQuestionID uniqueidentifier;
Declare @SQLSelect VarChar(100);
Declare @SQLToUse VarChar(8000);
Declare @SQLToUseJoin VarChar(8000);
Declare @SQLToExecute Varchar(8000);
Declare @Alias VarChar(10);
Declare @intCounter Int;
set @Question = '';
set @SQLSelect = '';
set @SQLToUse = '';
set @intCounter = 0;
set @Alias = '';
set @SQLToUseJoin = '';
----------------------------------------------------------------------------
-- DECLARE AND OPEN CURSOR USED TO BUILD TABLE
----------------------------------------------------------------------------
Declare curBubba Cursor
FOR
SELECT Question, DynamicQuestionID
FROM dnn_DynamicForms_Question
where QuestionType <> 'HTML'
AND QuestionType <> 'HR'
AND InActive=0
AND ModuleID = @ModuleID
ORDER BY SortOrder
FOR READ ONLY;
OPEN curBubba;
FETCH NEXT FROM curBubba INTO @Question, @DynamicQuestionID;
WHILE @@FETCH_STATUS = 0
BEGIN
WHILE CHARINDEX('/',@Question) > 0
BEGIN
SET @Question = SUBSTRING(@Question, 1, CHARINDEX('/',@Question)-1)
+ ' '
+ SUBSTRING(@Question, CHARINDEX('/',@Question) + 1, LEN(@Question))
END
WHILE CHARINDEX('.',@Question) > 0
BEGIN
SET @Question = SUBSTRING(@Question, 1, CHARINDEX('.',@Question)-1)
+ ' '
+ SUBSTRING(@Question, CHARINDEX('.',@Question) + 1, LEN(@Question))
END
WHILE CHARINDEX('(',@Question) > 0
BEGIN
SET @Question = SUBSTRING(@Question, 1, CHARINDEX('(',@Question)-1)
+ ' '
+ SUBSTRING(@Question, CHARINDEX('(',@Question) + 1, LEN(@Question))
END
WHILE CHARINDEX(')',@Question) > 0
BEGIN
SET @Question = SUBSTRING(@Question, 1, CHARINDEX(')',@Question)-1)
+ ' '
+ SUBSTRING(@Question, CHARINDEX(')',@Question) + 1, LEN(@Question))
END
WHILE CHARINDEX(' ',@Question) > 0
BEGIN
SET @Question = SUBSTRING(@Question, 1, CHARINDEX(' ',@Question)-1)
+ SUBSTRING(@Question, CHARINDEX(' ',@Question) + 1, LEN(@Question))
END
SET @Question = Ltrim(Rtrim(@Question))
set @intCounter = @intCounter + 1;
set @Alias = '[RJoin_' + CAST(@intCounter as varchar(20)) + ']';
if DataLength(@SQLToUse) = 0
BEGIN
--set @SQLToUse = 'SELECT A.uniqueresponseID, a.UserID, A.UserName, IsNull(' + @Alias + '.response,''N/A'') as [' + @Question + '] '
set @SQLSelect = 'SELECT '
set @SQLToUse = 'A.uniqueresponseID, a.UserID, A.UserName, IsNull(' + @Alias + '.response,''N/A'') as [' + @Question + '] '
set @SQLToUseJoin = '
From (
SELECT distinct Y.uniqueresponseID, Y.UserID, B.UserName
from [dnn_DynamicForms_QuestionResponse] As Y
inner join dnn_DynamicForms_Question as Z on (Y.DynamicQuestionID = Z.DynamicQuestionID)
Left Outer Join dnn_Users B on B.UserID = Y.UserID
where Z.ModuleID = ' + Cast(@ModuleID as varchar(20)) + '
) As A
Left Outer join dnn_DynamicForms_QuestionResponse as ' + @Alias + ' on (A.uniqueresponseID = ' + @Alias + '.uniqueresponseID and ''' + Cast(@DynamicQuestionID as varchar(50))+ ''' = ' + @Alias + '.DynamicQuestionID)
'
END
ELSE
BEGIN
set @SQLToUse = @SQLToUse + ', ' + @Alias + '.response as [' + @Question + '] '
set @SQLToUseJoin = @SQLToUseJoin + 'Left Outer join dnn_DynamicForms_QuestionResponse as ' + @Alias + ' on (A.uniqueresponseID = ' + @Alias + '.uniqueresponseID and ''' + Cast(@DynamicQuestionID as varchar(50))+ ''' = ' + @Alias + '.DynamicQuestionID)
'
END;
FETCH NEXT FROM curBubba INTO @Question, @DynamicQuestionID;
END
CLOSE curBubba;
DEALLOCATE curBubba;
set @SQLSelect = ltrim(rtrim(@SQLSelect)) + ' '
set @SQLToExecute = @SQLSelect + @SQLToUse + 'into tblExportFormResults ' + @SQLToUseJoin
--set @SQLToExecute = @SQLToUse + @SQLToUseJoin
if @PrintSQLToExecute = 'Y'
print @SQLToExecute
else
EXEC(@SQLToExecute)
SELECT *
FROM tblExportFormResults
-----------------------------------------------------------------
-- REMOVE TEMPORARY TABLES
-----------------------------------------------------------------
--if (SELECT object_id('tempdb..tblExportFormResults')) IS NOT NULL
-- drop table tblExportFormResults
--if exists (select * from sysobjects where id = object_id(N'dbo.tblExportFormResults') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
--drop table dbo.tblExportFormResults
----------------------------------------------------------------------------
-- TURN ON ROW COUNT DISPLAY & WARNING MESSAGES
----------------------------------------------------------------------------
SET NOCOUNT OFF
SET ANSI_WARNINGS ON
GO