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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy