sql - 在不使用 MAX、AVG 等聚合函数的情况下，将行转换为 SQL 中的列

Question

我有一个这样的表:

CREATE TABLE MyDataTable
(
     [RollNo] varchar(8), 
     [QuesBlock] int, 
     [RespVal] varchar(2)
);

INSERT INTO MyDataTable ([RollNo], [QuesBlock], [RespVal])
VALUES ('MBA0001', 1, A), ('MBA0001', 2, B), ('MBA0001', 3, D),
       ('MBA0002', 1, C), ('MBA0002', 2, A), ('MBA0002', 3, B),
       ('MBA0003', 1, B), ('MBA0003', 2, C), ('MBA0003', 3, A);

因此，我的源数据如下所示:

现在我想达到这样的目标表结构:

这基本上是在基于 OMR 的学校测试中，将每个问题的候选答案、问题答案制成表格。我在 QuesBlock 列中有固定数量的唯一值(仅 50)，因此我可以接受硬编码。我经历了examples其中 pivot 被用来实现类似的东西，但它们都使用了聚合函数，如 MAX、MIN、AVG 等来处理数值。但在我的例子中，RESPVAL 列的值都是文本的。我如何实现这一目标？

Answer 1

您可以对字符/字符串使用 max()。一个简单的旧式 pivot 将适用于此:

select
    RollNo
  , Q1 = max(case when QuesBlock = 1 then RespVal else null end)
  , Q2 = max(case when QuesBlock = 2 then RespVal else null end)
  , Q3 = max(case when QuesBlock = 3 then RespVal else null end)
from MyDataTable
group by RollNo;

或像这样使用 pivot():

select
    RollNo
  , Q1
  , Q2
  , Q3
from (select RollNo, QuesBlock='Q'+convert(varchar(2),QuesBlock), RespVal
      from MyDataTable) as i
 pivot (max(RespVal) for QuesBlock in (Q1,Q2,Q3)) as p;

或像这样动态地pivot():

declare @query nvarchar(max);
declare @cols nvarchar(max);

select @cols = stuff((select ','+quotename('Q'+convert(varchar(2),QuesBlock))
         from MyDataTable as C
         group by c.QuesBlock
         order by c.QuesBlock
         for xml path('')), 1, 1, '');
set @query = 'select RollNo, '+@cols+'
      from(select RollNo, QuesBlock=''Q''+convert(varchar(2),QuesBlock), RespVal
        from MyDataTable) as i
      pivot
      (
        max(RespVal)
        for QuesBlock in ('+@cols+')
      ) p';
exec sp_executesql @query;

测试设置:http://rextester.com/TURW69000

所有三个返回:

+---------+----+----+----+
| RollNo  | Q1 | Q2 | Q3 |
+---------+----+----+----+
| mba0001 | A  | B  | D  |
| mba0002 | C  | A  | B  |
| mba0003 | B  | C  | A  |
+---------+----+----+----+