First try at Pivot Table

  • I am trying to create a query with a pivot table which is not something I have done before.
    The query must be dynamic because I will not know how many  columns there will be.  In this example I am hard coding in the value but if you look at the commented out lines you can see it will actually be variables
    Here is the query as I have written it

    DECLARE @PivotQuery NVARCHAR(MAX)
    DECLARE @Cols varchar(max)
    SET @Cols = STUFF((SELECT distinct ',' + QUOTENAME(ResponseText,'''') FROM dbo.SurveyResponses WHERE QuestionID = 86 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

    --SELECT @Cols
    CREATE TABLE #Data
    (
        QuestionText varchar(400),
        ResponseText varchar(200),
        AverageAnswer float
    )
    INSERT #Data
        --EXECUTE AverageDemographicGroupAnswer @GroupID, @Survey, @Demographic
        EXECUTE AverageDemographicGroupAnswer 1,1,86

        SELECT * FROM #Data
    SET @PivotQuery = 'SELECT QuestionText, '+ @Cols +' FROM (SELECT QuestionText, ResponseText, '+ @Cols +' FROM #Data) PIVOT( AverageAnswer FOR ResponseText in (' + @Cols + '))'
    --SELECT @PivotQuery
    EXECUTE(@PivotQuery)

    DROP Table #Data

    And here is what the results of SELECT @PivotQuery returned

    SELECT QuestionText, 'At least 1 year but less than 3 years','At least 3 years but less than 5 years','Less than 1 year','More than 5 years' FROM (SELECT QuestionText, ResponseText, 'At least 1 year but less than 3 years','At least 3 years but less than 5 years','Less than 1 year','More than 5 years' FROM #Data)
    PIVOT( AverageAnswer FOR ResponseText in ('At least 1 year but less than 3 years','At least 3 years but less than 5 years','Less than 1 year','More than 5 years'))

    here is the data that is held in the temp table 

    QuestionText                                                                                        ResponseText                               AverageAnswer
    I consider Big Tex Bank to be diverse, reflecting different ages, ethnicities, and backgrounds.     Less than 1 year                          4.00
    I consider Big Tex Bank to be diverse, reflecting different ages, ethnicities, and backgrounds.     At least 1 year but less than 3 years     4.67
    I consider Big Tex Bank to be diverse, reflecting different ages, ethnicities, and backgrounds.     At least 3 years but less than 5 years    4.50
    Big Tex Bank encourages and supports a healthy lifestyle, and work/life balance.                    Less than 1 year                          4.00
    Big Tex Bank encourages and supports a healthy lifestyle, and work/life balance.                    At least 1 year but less than 3 years     4.00
    Big Tex Bank encourages and supports a healthy lifestyle, and work/life balance.                    At least 3 years but less than 5 years    4.50
    I look forward to coming to work every day.                                                         Less than 1 year                          5.00
    I look forward to coming to work every day.                                                         At least 1 year but less than 3 years     4.00
    I look forward to coming to work every day.                                                         At least 3 years but less than 5 years    4.50
    I would refer a friend to work here.                                                                Less than 1 year                          5.00
    I would refer a friend to work here.                                                                At least 1 year but less than 3 years     4.67
    I would refer a friend to work here.                                                                At least 3 years but less than 5 years    4.50

  • I found one thing wrong in how I am enclosing the columns.  so instead of using apostrophes I am using brackets  like so

    SET @Cols = STUFF((SELECT distinct ',' + QUOTENAME(ResponseText,'[]') FROM dbo.SurveyResponses WHERE QuestionID = 86 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

    this produces the following

    SELECT QuestionText, [At least 1 year but less than 3 years],[At least 3 years but less than 5 years],[Less than 1 year],[More than 5 years] FROM (SELECT QuestionText, AverageAnswer, ResponseText FROM #Data)
    PIVOT( AverageAnswer FOR ResponseText in ([At least 1 year but less than 3 years],[At least 3 years but less than 5 years],[Less than 1 year],[More than 5 years]))

    However, that gives the error
    Incorrect syntax near the keyword 'PIVOT'.

    so I changed the syntax to

    SET @PivotQuery = 'SELECT QuestionText, '+ @Cols +' FROM (SELECT QuestionText, AverageAnswer, ResponseText FROM #Data) AS SourceTable PIVOT( AverageAnswer FOR ResponseText in (' + @Cols + ')) AS PivotTable '

    This is the query the above produces

    SELECT QuestionText, [At least 1 year but less than 3 years],[At least 3 years but less than 5 years],[Less than 1 year],[More than 5 years] FROM (SELECT QuestionText, AverageAnswer, ResponseText FROM #Data) AS SourceTable
    PIVOT( AverageAnswer FOR ResponseText in ([At least 1 year but less than 3 years],[At least 3 years but less than 5 years],[Less than 1 year],[More than 5 years])) AS PivotTable

    and that gives me the following error
    Incorrect syntax near the keyword 'FOR'.

  • it is working   needed to aggregate the data.  so my final query is 

    SET @PivotQuery = 'SELECT QuestionText, '+ @Cols +' FROM (SELECT QuestionText, AverageAnswer, ResponseText FROM #Data) AS SourceTable PIVOT( MAX(AverageAnswer) FOR ResponseText in (' + @Cols + ')) AS PivotTable '

  • So you're all set then?

    --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)

  • Jeff Moden - Thursday, June 29, 2017 7:59 PM

    So you're all set then?

    Yes.

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

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