SQL Server 2008 - 以一列为中心，按另一列分组，保持对第三列的引用 [英] SQL Server 2008 - Pivot on one column, group by another, maintain reference to third

问题描述

我刚刚开始学习 SQL，正在努力正确转置三列表.

I've just started learning SQL and am struggling with transposing a three column table correctly.

这是我的起点:

questionid | DebriefingQuestionResults | OperationSessionRecordID
------------------------------------------------------------------
32         |        3                  | 8071
34         |        0                  | 8071
36         |        1                  | 8071
32         |        2                  | 8074
34         |        6                  | 8074
36         |        5                  | 8074

这是我想要制作的:

    OperationSessionRecordID | 32 | 34 | 36
----------------------------------------------
    8071                     | 3  | 0  | 1       
    8074                     | 2  | 6  | 5

只有三个 [questionid] 值(32、34 和 36)，但大约有 12000 个 [OperationSessionRecordID] 值.所有列都是 int not null 类型.

There are only three [questionid] values (32, 34, and 36), but about 12000 [OperationSessionRecordID] values. All columns are of the type int not null.

我知道我需要从我的输入表和 [OperationSessionRecordID] 中对 [questionid] 进行反透视/透视，但查看了 无数 其他 问题 我仍然很困惑.

I know I need to unpivot/pivot on [questionid] from my input table and group by [OperationSessionRecordID], but having looked at numerous other questions I'm still very stuck.

基于这个答案到目前为止我已经知道了，但我不确定如何继续使用单个表，因此不需要内部联接.

Based on this answer I've got this so far, but am not sure how to proceed as I'm working with a single table and therefore don't need the inner join.

SELECT [32], [34], [36]
FROM
(
    SELECT A.questionid, A.DebriefingQuestionResults
    , row_number() over(partition by a.OperationSessionRecordID)
    FROM dbo.TEMPtable AS A
) AS P
PIVOT
(
  min(P.Value)
  for P.questionid in ([32], [34], [36])
) AS PIV;

当我运行它时它会抛出这个错误，所以我对 row_number 的使用显然有问题.

When I run this it throws this error, so something's obviously wrong with my use of row_number.

消息 4112，级别 15，状态 1，第 6 行排名函数row_number"必须有一个 ORDER BY 子句.

Msg 4112, Level 15, State 1, Line 6 The ranking function "row_number" must have an ORDER BY clause.

任何帮助将不胜感激！

SB

推荐答案

这是一个非常简单的支点.只是:

That's a very straightforward pivot. Just:

SELECT OperationSessionRecordID, [32],[34],[38]
FROM TEMPtable
  PIVOT (
  min(DebriefingQuestionResults)
  for questionid in ([32], [34], [36])
) AS PIV;

您无需执行任何特殊操作即可通过 OperationSessionRecordID 获得分组 - 基本上，PIVOT 中未提及的每一列都是分组列.

You don't need to do anything special to get the grouping by OperationSessionRecordID - essentially, every column not mentioned in the PIVOT is a grouping column.

这篇关于SQL Server 2008 - 以一列为中心，按另一列分组，保持对第三列的引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！