February 2, 2006 at 9:20 am
I want to create views dynamically.
CREATE TABLE SurveyQuestion(
QuestionID int Null,
SurveyID int NULL,
QuestionText Varchar(100) Null,
SortOrder int)
GO
Insert into SurveyQuestion values(1, 1, 'First Visit?', 1)
Insert into SurveyQuestion values(2, 1, 'Did you like the Site?', 2)
Insert into SurveyQuestion values(3, 1, 'Would you recommend us to a friend?', 3)
Insert into SurveyQuestion values(4, 2, 'Are you a fan of Football?', 1)
Insert into SurveyQuestion values(5, 2, 'Who do you think will win the SuperBowl?', 2)
Insert into SurveyQuestion values(6, 2, 'Will you return to our site?',3)
Insert into SurveyQuestion values(7, 2, 'last question?',4)
Insert into SurveyQuestion values(8, 3, 'Just 1 question?',1)
I want to dynamically build a view for each of these surveys where the questiontext is the column headers, so i can display the answers for each question beneath.
Example - SurveyID1 would look like this
ViewSurvey1
RespondentID First Visit? Did you like our site? Would you recommend us to a friend?
1 Yes Yes Yes
Example - SurveyID 3
ViewSurvey3
RespondentID Just 1 Question?
1 1 question too many
February 2, 2006 at 2:03 pm
I would suggest trigger on his table.
This trigger suppose to recreate view(s) after each INSERT/UPDATE/DELETE on this table.
_____________
Code for TallyGenerator
February 3, 2006 at 8:04 am
Im not sure how a trigger would solve this problem. I either want to run a stored procedure to create the views dynamically based on a schedule or allow a stored proc to pass in the surveyid and have the view created at run time. I want to get out of the business of creating these views manually everytime a new survey is created.
February 3, 2006 at 9:51 am
abstract back out one level.
create a stored procedure where you pass the surveyid, and it passes back the questions.
then you have one stored procedure for ALL of your surveys.
February 3, 2006 at 8:26 pm
John,
I didn't take the time to convert some of my old code to your specific example but the following code is a good example of a "dynamic crosstab". Since the example uses temp tables, you may run it with impunity.
Doug has the right idea about using something similar as a stored proc instead of a view...
--===== Preset to suppress counts for speed and
-- "clean" display
SET NOCOUNT ON
--===== If test table exists, drop it
IF OBJECT_ID('TempDB..#tblSignUp') IS NOT NULL
DROP TABLE #tblSignUp
--===== Create the test table
CREATE TABLE #tblSignUp
(
ID INT IDENTITY (1,1),
ProjectID VARCHAR(20),
UserID VARCHAR(20),
PointsEarned INT
)
--===== Load test table with given data
INSERT INTO #tblSignUp
(ProjectID,UserID,PointsEarned)
SELECT 'proj1','user1',1 UNION ALL
SELECT 'proj2','user2',5 UNION ALL
SELECT 'proj1','user3',5 UNION ALL
SELECT 'proj2','user3',5 UNION ALL
SELECT 'proj3','user3',6
--===== Verifiy the load
PRINT 'Here''s what the table data looks like...'
SELECT * FROM #tblSignUp
--===== Preset the dynamic SQL
DECLARE @MySQL VARCHAR(8000)
SET @MySQL = 'SELECT UserID,'+CHAR(13)
--===== Build the guts of the dynamic SQL for each Seris value
SELECT @MySQL = @MySQL
+ 'SUM(CASE ProjectID WHEN '''
+ d.ProjectID
+ ''' THEN PointsEarned ELSE 0 END) AS '
+ UPPER(d.ProjectID) + ',' + CHAR(13)
FROM
( --Derived table gets unique Seris values
SELECT DISTINCT TOP 100 PERCENT ProjectID
FROM #tblSignUp s
ORDER BY ProjectID
) d
--===== Finish up the dynamic SQL
SET @MySQL = @MySQL
+ 'SUM(PointsEarned) AS TotalPointsEarned' + CHAR(13)
+ 'FROM #tblSignUp GROUP BY UserID'
--===== Display the dynamic SQL just for grins...
PRINT 'This is what the dynamic SQL looks like...'
PRINT '------------------------------------------------------'
PRINT @MySQL
PRINT '------------------------------------------------------'
PRINT ''
--===== Execute the dynamic SQL
PRINT 'And, this is what the output looks like...'
EXEC (@MySQL)
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply