临床试验患者访视数据重组：如何添加C1D4访视日期字段

你的原SQL问题在于子查询没有和外层的Subject关联，会返回所有符合访视名称的记录，无法精准定位到当前患者的对应日期。用条件聚合可以轻松实现行转列，把同一患者的不同访视日期放到同一行，之后再关联分组表即可。

第一步：处理访视日期行转列 #

先对DateOfVisit表按患者ID分组，用CASE WHEN提取对应访视的日期：

SELECT
  Subject AS SubjectID,
  MAX(CASE WHEN InstanceName = 'Safety Visit Cycle 01 Day 01' THEN Field3Value END) AS C1D1VisitDate,
  MAX(CASE WHEN InstanceName = 'Safety Visit Cycle 01 Day 04' THEN Field3Value END) AS C1D4VisitDate
FROM `myproject.patients.DateOfVisit`
GROUP BY Subject
ORDER BY Subject DESC;

这里用MAX（或MIN，因为每个患者对应一个访视日期只会有一条记录）来聚合，确保每个患者只返回一行，同时筛选出对应访视的日期值。

第二步：关联分组信息表 #

把上面的结果作为子查询，和CohortInfo表通过SubjectID关联，补充分组信息：

SELECT
  dv.SubjectID,
  ci.`Cohort Assignment`,
  dv.C1D1VisitDate,
  dv.C1D4VisitDate
FROM (
  SELECT
    Subject AS SubjectID,
    MAX(CASE WHEN InstanceName = 'Safety Visit Cycle 01 Day 01' THEN Field3Value END) AS C1D1VisitDate,
    MAX(CASE WHEN InstanceName = 'Safety Visit Cycle 01 Day 04' THEN Field3Value END) AS C1D4VisitDate
  FROM `myproject.patients.DateOfVisit`
  GROUP BY Subject
) dv
LEFT JOIN `myproject.patients.CohortInfo` ci
ON dv.SubjectID = ci.SubjectID
ORDER BY dv.SubjectID DESC;

用LEFT JOIN可以保留所有有访视记录的患者，即使分组表中没有对应记录（如果要求必须有分组信息，换成INNER JOIN即可）。

关键说明 #

• 条件聚合是处理这类“行转列”需求的标准方案，适合同一实体（患者）有多条属性记录（不同访视日期）的场景。
• 原SQL的子查询没有限定Subject = outer.Subject，导致返回的是全局匹配的记录，而非当前患者的，这是核心问题。

内容的提问来源于stack exchange，提问作者Hwa Fine Han