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

1 Answers 11

up vote 15 down vote accepted

Yes you can perform a dynamic pivot. Sometimes it is easier to work up the PIVOT query using a static version first so you can see how the query and results will appear. Then transform the query into a dynamic version.

Here is an example of a static vs. dynamic version of a query:

Static (SQL Fiddle):

select *
from 
(
    select u.userid,
        u.fname,
        u.lname,
        u.mobile,
        r.question,
        r.choice
    from users u
    left join results r
        on u.questionid = r.questionid
        and u.choiceid = r.choiceid
) x
pivot
(
    min(choice)
    for question in([are you], [from])
) p

Dynamic (SQL Fiddle):

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.question) 
            FROM results c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT userid, fname, lname, mobile, ' + @cols + ' from 
            (
                select u.userid,
                    u.fname,
                    u.lname,
                    u.mobile,
                    r.question,
                    r.choice
                from users u
                left join results r
                    on u.questionid = r.questionid
                    and u.choiceid = r.choiceid
           ) x
            pivot 
            (
                min(choice)
                for question in (' + @cols + ')
            ) p '


execute(@query)

If you can provide more details around your current table structure and then some sample data. We should be able to help you create the version that you would need for your situation.

As I said though, sometimes it is easier to start with a static version, where you hard-code in the columns that you need to transform first, then move on to the dynamic version.

upvote
  flag
In your example, what datatype does the 'choice'-field have? I have a varchar field which contains the answer to the question. – Michel van Engelen
upvote
  flag
It is a varchar if you look at the SQL Fiddle it will show the table structure. – bluefeet
upvote
  flag
I've got it working, with MIN(QuestionName). I wasn't expecting that! Thanks for your help Blue :). – Michel van Engelen
upvote
  flag
Thank so much for the query and it will execute successfully at my side but my problem is that how to bind the columns value with DataReader or DataSet. pl tell me how to do this stuff. – Harshal
upvote
  flag
@Harshal You need to Ask A new Question and include all relevant details include your code, query, etc. – bluefeet

Not the answer you're looking for? Browse other questions tagged or ask your own question.