I have questionnaire data in, SQL Server 2008, that I want to transpose to a matrix.
I saw several posts about the same topic, but I just don't get pivoting.
Given are following tables:
[QuestionName_n] <- dynamic number of question columns)
The code to retrieve the columns:
DECLARE @columns VARCHAR(8000) SELECT @columns = COALESCE(@columns + ',[' + cast(QuestionName as varchar) + ']', '[' + cast(QuestionName as varchar)+ ']') FROM Answer A INNER JOIN Question Q ON A.QuestionID = Q.QuestionID INNER JOIN Customer C ON A.CustomerID = C.CustomerID GROUP BY Q.QuestionName SET @columns = '[CustomerID],' + @columns DECLARE @query VARCHAR(8000) SET @query = 'Some PIVOT query without aggregation' EXECUTE(@query)
The initial query idea was taken from pivots with dynamic columns.
Can it be done and what would the pivoting query look like?
ps: I don't want to use ranking with a maximum number of columns.