Geek asks and answers

Dynamic columns with a rotary contour, without aggregation

I have polling data in SQL Server 2008 that I want to pass to a matrix.
I saw several posts on the same topic, but I just don't make a turn.

The following tables are listed below:

Question table

Answer table

Customer table

Columns:
[CustomerID] , [QuestionName_1], .., [QuestionName_n]<- a dynamic number of questions column)
Data:
CustomerID , Answer_1, ..,Answer_n

Code for extracting 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 original query idea was taken from turning points with dynamic columns .

Can this be done and what will the turning request look like?
ps: I do not want to use ranking with the maximum number of columns.

Hi,

Michelle

+5
1

, . PIVOT, , , . .

:

(SQL-):

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

(SQL-):

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)

, . , .

, , , , .

+15

More articles:


All Articles