枢轴动态列，无聚合 [英] Pivot Dynamic Columns, no Aggregation

问题描述

我在SQL Server 2008中具有要转换为矩阵的调查表数据.
我看到了关于同一主题的几篇文章，但我只是不作讨论.

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.

给出以下表格:

Question table

Answer table

Customer table

列:
[CustomerID]，[QuestionName_1]，..，[QuestionName_n]<-动态问题列数)
数据:
CustomerID，Answer_1，..，Answer_n

The columns:
[CustomerID], [QuestionName_1], .., [QuestionName_n] <- dynamic number of question columns)
The data:
CustomerID, Answer_1, .., Answer_n

检索列的代码:

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)

最初的查询思路来自带有动态列的数据.

可以做到吗，透视查询会是什么样?
ps:我不想使用列数最多的排名.

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.

此致

米歇尔

推荐答案

是的，您可以执行动态数据透视.有时更容易处理 PIVOT 查询首先使用静态版本，这样您就可以查看查询和结果的显示方式.然后将查询转换为动态版本.

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:

静态( 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)

如果您可以提供有关当前表结构的更多详细信息，然后提供一些示例数据.我们应该能够帮助您创建适合您的情况的版本.

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.

这篇关于枢轴动态列，无聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！