First try at Pivot Table

Question

First try at Pivot Table

mjohnson 71479

SSC Eights!

Points: 846
More actions
June 28, 2017 at 5:03 pm

#104461

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

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

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

mjohnson 71479 SSC Eights! Points: 846 More actions · Answer 1

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

mjohnson 71479 SSC Eights! Points: 846 More actions · Answer 2

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 '

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

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)

mjohnson 71479 SSC Eights! Points: 846 More actions · Answer 4

Jeff Moden - Thursday, June 29, 2017 7:59 PM
So you're all set then?

Yes.