June 28, 2017 at 5:03 pm
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
June 29, 2017 at 2:09 pm
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'.
June 29, 2017 at 5:13 pm
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 '
June 29, 2017 at 7:59 pm
So you're all set then?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 30, 2017 at 8:06 am
Jeff Moden - Thursday, June 29, 2017 7:59 PMSo 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