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:
Question table
Answer table
Customer table
The columns:
[CustomerID], [QuestionName_1], .., [QuestionName_n] <- dynamic number of question columns)
The data:
CustomerID, Answer_1, .., Answer_n
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.
Regards,
Michel