如何将行旋转到列 [英] how to pivot rows to columns

问题描述

在MSSQL中使用PIVOT的每个示例都显示出人们正在使用它来聚合数据.我正在尝试利用它来简单地将行转置为列

Every example of using PIVOT in MSSQL shows people using this to aggregate data. I'm trying to exploit this to just simply transpose rows to columns

例如，考虑以下数据

   SELECT 11826 ID,cast('64 ' as varchar(1000)) as answer,75098 QuestionID,2785 CollectionID  into #temp
insert into #temp SELECT 11827 ID,cast('Security ' as varchar(1000)) as answer,75110 QuestionID,2785 CollectionID 
insert into #temp SELECT 11828 ID,cast('42 ' as varchar(1000)) as answer,75115 QuestionID,2785 CollectionID 
insert into #temp SELECT 11829 ID,cast('3/23/2010 12:01:00 AM ' as varchar(1000)) as answer,75119 QuestionID,2785 CollectionID 
insert into #temp SELECT 11830 ID,cast('3/25/2010 ' as varchar(1000)) as answer,75120 QuestionID,2785 CollectionID 
insert into #temp SELECT 11898 ID,cast('67 ' as varchar(1000)) as answer,75313 QuestionID,2792 CollectionID 
insert into #temp SELECT 11899 ID,cast('True ' as varchar(1000)) as answer,75314 QuestionID,2792 CollectionID 
insert into #temp SELECT 11900 ID,cast('0 ' as varchar(1000)) as answer,75315 QuestionID,2792 CollectionID 
insert into #temp SELECT 11901 ID,cast('[e-mail] ' as varchar(1000)) as answer,75316 QuestionID,2792 CollectionID

结果应该类似于

CollectionID   [AnswerFor75098]     [AnswerFor75110]     [AnswerFor75115]     [AnswerFor75315]...
2785             64                  Security            42                   
2792             Null               Null                 Null                  67

我一直在尝试PIVOT，但是我不确定这是否是正确的解决方案.如果是这样，有人有我可以使用的提示吗? 我想我可能可以在存储过程中做到这一点，但是，我试图避免使用游标(如果可能).

I've been experimenting with PIVOT however i'm not sure this is the correct solution. If so, does anybody have a hint i could use? I think i can probably do this in a stored procedure fairly however, i'm trying to avoid using cursors if possible.

感谢您的帮助

推荐答案

您要查找的是交叉表查询.如果您知道提前查询的问题编号，可以这样进行:

What you are seeking is a crosstab query. If you know the question numbers you seek ahead of time, you can do it like so:

Select CollectionId
    , Min( Case When Question = 75098 Then Answer End ) As AnswerFor75098
    , Min( Case When Question = 75110 Then Answer End ) As AnswerFor75110
    , Min( Case When Question = 75115 Then Answer End ) As AnswerFor75115
    , Min( Case When Question = 75315 Then Answer End ) As AnswerFor75315
From #Temp
Group By CollectionId

但是，如果要基于问题动态确定列，则需要的是动态交叉表，而如果没有一些笨拙的动态SQL，则无法在SQL Server中完成.相反，您应该在中间层或报告工具中执行此操作.

However, if what you want is to dynamically determine the columns based on questions, then what you want is a dynamic crosstab and that cannot be done in SQL Server without some fugly dynamic SQL. Instead, you should do this in the middle-tier or in a reporting tool.

这篇关于如何将行旋转到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！