Build Views Dynamically

  • 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

  • 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

  • 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.

  • 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.

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply