透视动态列，无聚合 [英] 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.

给出以下表格:

问题表

答案表

客户表

列:
[CustomerID], [QuestionName_1], .., [QuestionName_n] <- dynamic 问题列数)
数据:
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)

最初的查询思路取自 具有动态列的枢轴.

The initial query idea was taken from pivots with dynamic columns.

是否可以完成，透视查询会是什么样子?
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 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

动态(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.

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