SQL行转列（Pivoting）问题：现有表格转置实现失败求解决

原表结构及数据 #

declare @tab TABLE (Id int, Rid INT, FValue VARCHAR(50), FText VARCHAR(50))

INSERT INTO @tab VALUES
   (1,1,'Patient','P1'),
   (1,1,'Sdate','01/06/2023'),
   (1,1,'Acc#','123265'),
   (1,1,'Status','New'),
   (1,2,'Patient','P2'),
   (1,2,'Sdate','02/06/2023'),
   (1,2,'Acc#','512364'),
   (1,2,'Status','New'),
   (1,3,'Patient','P3'),
   (1,3,'Sdate','03/06/2023'),
   (1,3,'Acc#','632154'),
   (1,3,'Status','Complete')

select * from @tab

错误的行转列尝试代码 #

select RNo, Patient, Sdate, Acc#,[Status]
from
(
  select Rid, FValue, FText
  from @tab
) d
pivot
(
  max(Rid) for FText in (Rno, Patient, Sdate, Acc#,[Status])
) piv;

期望的目标表格 #

declare @tab1 TABLE (Rno int, Patient VARCHAR(50), Sdate Datetime, Acc# VARCHAR(50), [Status] VARCHAR(50))

INSERT INTO @tab1 
VALUES
   (1,'P1','01/06/2023','123265','New'), 
   (2,'P2','02/06/2023','512364','New'), 
   (3,'P3','03/06/2023','632154','Complete')

select * from @tab1

正确的行转列实现代码 #

原代码核心问题是聚合对象错误、pivot列定义不符合逻辑。正确实现需以Rid为分组依据，将FText的取值转为列名，聚合对应行的FValue作为列内容：

select 
    Rid as RNo,
    Patient,
    Sdate,
    [Acc#],
    [Status]
from
(
    select Rid, FValue, FText
    from @tab
) d
pivot
(
    max(FValue) 
    for FText in (Patient, Sdate, [Acc#], [Status])
) piv;

关键修正点： #

• 聚合目标改为max(FValue)：需要提取的是每个FText对应的实际内容（FValue），而非重复聚合Rid
• 移除pivot列中的Rno：Rno是原表Rid的别名，不属于FText的取值范围
• 最终查询时将Rid重命名为RNo，匹配目标表结构

内容的提问来源于stack exchange，提问作者Rahul Aggarwal