SQL Server 2005 在未知列数上透视
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/213702/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2005 Pivot on Unknown Number of Columns
提问by Mitchel Sellers
I am working with a set of data that looks something like the following.
我正在处理一组类似于以下内容的数据。
StudentName | AssignmentName | Grade --------------------------------------- StudentA | Assignment 1 | 100 StudentA | Assignment 2 | 80 StudentA | Total | 180 StudentB | Assignment 1 | 100 StudentB | Assignment 2 | 80 StudentB | Assignment 3 | 100 StudentB | Total | 280
StudentName | AssignmentName | Grade --------------------------------------- StudentA | Assignment 1 | 100 StudentA | Assignment 2 | 80 StudentA | Total | 180 StudentB | Assignment 1 | 100 StudentB | Assignment 2 | 80 StudentB | Assignment 3 | 100 StudentB | Total | 280
The name and number of assignments are dynamic, I need to get results simlilar to the following.
分配的名称和数量是动态的，我需要获得类似于以下的结果。
Student | Assignment 1 | Assignment 2 | Assignment 3 | Total -------------------------------------------------------------------- Student A | 100 | 80 | null | 180 Student B | 100 | 80 | 100 | 280
Student | Assignment 1 | Assignment 2 | Assignment 3 | Total -------------------------------------------------------------------- Student A | 100 | 80 | null | 180 Student B | 100 | 80 | 100 | 280
Now ideally I would like to sort the column based on a "due date" that could be included/associated with each assignment. The total should be at the end if possible (It can be calculated and removed from the query if possible.)
现在理想情况下，我想根据可以包含/与每个作业关联的“截止日期”对列进行排序。如果可能，总数应该在最后（如果可能，可以计算并从查询中删除。）
I know how to do it for the 3 assignments using pivot with simply naming the columns, it is trying to do it in a dynamic fashion that I haven't found a GOOD solution for yet. I am trying to do this on SQL Server 2005
我知道如何使用pivot 完成3 个任务并简单地命名列，它正在尝试以一种动态的方式来完成，但我还没有找到一个好的解决方案。我正在尝试在 SQL Server 2005 上执行此操作
EDIT
编辑
Ideally I would like to implement this WITHOUT using Dynamic SQL, as that is against the policy. If it isn't possible...then a working example with Dynamic SQL will work.
理想情况下，我想在不使用动态 SQL 的情况下实现这一点，因为这违反了政策。如果不可能……那么动态 SQL 的工作示例将起作用。
采纳答案by Cade Roux
I know you said no dynamic SQL, but I don't see any way to do it in straight SQL.
我知道你说没有动态SQL，但我看不出有什么方法可以直接做到这一点SQL。
If you check out my answers to similar problems at Pivot Table and Concatenate Columnsand PIVOT in sql 2005
如果您在 sql 2005 中的Pivot Table and Concatenate Columnsand PIVOT查看我对类似问题的回答
The dynamic SQLthere is not vulnerable to injection, and there is no good reason to prohibit it. Another possibility (if the data is changing very infrequently) is to do code-generation - instead of dynamic SQL, the SQLis generated to a stored procedure on a regular basis.
SQL那里的动态不容易受到注入，也没有充分的理由禁止它。另一种可能性（如果数据很少更改）是进行代码生成 - 而不是 dynamic SQL，而是SQL定期生成存储过程。
回答by Taryn
To PIVOTthis data using dynamic sql you can use the following code in SQL Server 2005+:
对于PIVOT使用动态 sql 的这些数据，您可以在 SQL Server 2005+ 中使用以下代码：
Create Table:
创建表：
CREATE TABLE yourtable
([StudentName] varchar(8), [AssignmentName] varchar(12), [Grade] int)
;
INSERT INTO yourtable
([StudentName], [AssignmentName], [Grade])
VALUES
('StudentA', 'Assignment 1', 100),
('StudentA', 'Assignment 2', 80),
('StudentA', 'Total', 180),
('StudentB', 'Assignment 1', 100),
('StudentB', 'Assignment 2', 80),
('StudentB', 'Assignment 3', 100),
('StudentB', 'Total', 280)
;
Dynamic PIVOT:
动态枢轴：
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(AssignmentName)
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT StudentName, ' + @cols + ' from
(
select StudentName, AssignmentName, grade
from yourtable
) x
pivot
(
min(grade)
for assignmentname in (' + @cols + ')
) p '
execute(@query)
The result is:
结果是：
| STUDENTNAME | ASSIGNMENT 1 | ASSIGNMENT 2 | ASSIGNMENT 3 | TOTAL |
--------------------------------------------------------------------
| StudentA | 100 | 80 | (null) | 180 |
| StudentB | 100 | 80 | 100 | 280 |
回答by BoltBait
The only way I've found to do this is to use dynamic SQL and put the column labels into a variable.
我发现这样做的唯一方法是使用动态 SQL 并将列标签放入变量中。
回答by tsilb
you could query information_schema to get the column names and types, then use the result as a subquery when you build your result set. Note you'll likely have to change the login's access a bit.
您可以查询 information_schema 以获取列名和类型，然后在构建结果集时将结果用作子查询。请注意，您可能需要稍微更改登录的访问权限。
回答by Booji Boy
This is the same as PIVOT in sql 2005
If this data is for consumption in a report you could use a SSRS matrix. It will generate columns dynamically from result set. I've used it many times - it works quite well for dynamic crosstab reports.
如果此数据用于报告中的使用，您可以使用 SSRS 矩阵。它将从结果集中动态生成列。我已经多次使用它 - 它非常适合动态交叉表报告。
Here's a good example w/ dynamic sql. http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx
这是一个带有动态 sql 的好例子。 http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx
回答by Chirag Patel
SELECT TrnType
INTO #Temp1
FROM
(
SELECT '[' + CAST(TransactionType AS VARCHAR(4)) + ']' AS TrnType FROM tblPaymentTransactionTypes
) AS tbl1
SELECT * FROM #Temp1
SELECT * FROM
(
SELECT FirstName + ' ' + LastName AS Patient, TransactionType, ISNULL(PostedAmount, 0) AS PostedAmount
FROM tblPaymentTransactions
INNER JOIN emr_PatientDetails ON tblPaymentTransactions.PracticeID = emr_PatientDetails.PracticeId
INNER JOIN tblPaymentTransactionDetails ON emr_PatientDetails.PatientId = tblPaymentTransactionDetails.PatientID
AND tblPaymentTransactions.TransactionID = tblPaymentTransactionDetails.TransactionID
WHERE emr_PatientDetails.PracticeID = 152
) tbl
PIVOT (SUM(PostedAmount) FOR [TransactionType] IN (SELECT * FROM #Temp1)
) AS tbl4
回答by Prasad
select studentname,[Assign1],[Assign2],[Assign3],[Total]
from
(
select studentname, assignname, grade from student
)s
pivot(sum(Grade) for assignname IN([Assign1],[Assign2],[Assign3],[Total])) as pvt
