sql - 通过ID将多行转换为一行

Question

我想要实现的是按 id 对它们进行分组，并为 date 和 data 创建一个列。

数据集的背景是参与者的实验室结果，由于禁食限制 n 等原因，一些测试无法在同一天进行。我使用的数据库是 SQL Server。

下面是我的数据集以及所需的输出。

示例数据集:

create table Sample 
(
      Id int,
      LAB_DATE date,
      A_CRE_1 varchar(100),
      B_GLUH_1 varchar(100),
      C_LDL_1 varchar(100),
      D_TG_1 varchar(100),
      E_CHOL_1 varchar(100),
      F_HDL_1 varchar(100),
      G_CRPH_1 varchar(100),
      H_HBA1C_1 varchar(100),
      I_GLU120_1 varchar(100),
      J_GLUF_1 varchar(100),
      K_HCR_1 varchar(100)
)

insert into Sample(Id, LAB_DATE,A_CRE_1, B_GLUH_1,C_LDL_1,E_CHOL_1,F_HDL_1,H_HBA1C_1,K_HCR_1)
values (01, '2017-11-21', '74', '6.4', '2.04', '4.17', '1.64', '6.1', '2.54')

insert into sample (Id, LAB_DATE, I_GLU120_1) 
values (01, '2017-11-22','8.8')

insert into sample (Id, LAB_DATE, D_TG_1) 
values (01, '2017-11-23','0.56')

insert into sample (Id,LAB_DATE,A_CRE_1,B_GLUH_1,C_LDL_1,D_TG_1,E_CHOL_1,F_HDL_1,K_HCR_1)       
values (2,'2018-10-02','57','8.91','2.43','1.28','3.99','1.25','3.19')

insert into sample (Id,LAB_DATE,H_HBA1C_1)                              
values (2,'2018-10-03','8.6')                       

insert into sample (Id,LAB_DATE,J_GLUF_1)                               
values (2,'2018-10-04','7.8')

insert into sample (Id,LAB_DATE,A_CRE_1,B_GLUH_1,C_LDL_1,D_TG_1,E_CHOL_1,F_HDL_1,G_CRPH_1,H_HBA1C_1,K_HCR_1)
values (3,'2016-10-01','100','6.13','3.28','0.94','5.07','1.19','0.27','5.8','4.26')

期望的输出:

ID|LAB_DATE|A_CRE_1|B_GLUH_1|C_LDL_1|Date_TG_1|D_TG_1|E_CHOL_1|F_HDL_1|G_CRPH_1|H_HBA1C_1|Date_GLU120_1|I_GLU120_1|J_GLUF_1|K_HCR_1
1|2017-11-21|74|6.4|2.04|2017-11-23|0.56|4.17|1.64|||6.1|2017-11-22|8.8|||2.54
2|02/10/2018|57|8.91|2.43||1.28|3.99|1.25||03/10/2018|8.6|||04/10/2018|7.8|3.19
3|01/10/2016|100|6.13|3.28||0.94|5.07|1.19|0.27||5.8|||||4.26

Answer 1

这是一个解决方案(无法处理相同 id/sample 类型的多行 - 你还没有说如何处理这些)

select * from

  (select Id, LAB_DATE,A_CRE_1, B_GLUH_1,C_LDL_1,E_CHOL_1,F_HDL_1,H_HBA1C_1,K_HCR_1 from sample) s1
  INNER JOIN
  (select Id, LAB_DATE as glu120date, I_GLU120_1 from sample) s2
  ON s1.id = s2.id
  (select Id, LAB_DATE as dtgdate, D_TG_1 from sample) s3
  ON s1.id = s3.id

希望您对这个模式有所了解；如果您有其他具有自己日期的样本类型，请将它们从 s1 中分离出来，并以类似的方式进入它们自己的子查询(例如，为 e_chol_1 创建一个 s4，为 k_hcr_1 等创建一个 s5)。请注意，如果任何样本类型是缺少它会导致整行从结果中消失。如果这是不需要的，并且您接受 NULL 作为缺失样本，请使用 LEFT JOIN 而不是 INNER

如果患者 01 有多个样本，而您只想要最新的样本，则模式变为:

select * from

  (select Id, LAB_DATE,A_CRE_1, B_GLUH_1,C_LDL_1,E_CHOL_1,F_HDL_1,H_HBA1C_1,K_HCR_1,
   row_number() over(partition by id order by lab_date desc) rn
   from sample) s1
  INNER JOIN
  (select Id, LAB_DATE as glu120date, I_GLU120_1,
   row_number() over(partition by id order by lab_date desc) rn
   from sample) s2
  ON s1.id = s2.id and s1.rn = s2.rn
WHERE
  s1.rn = 1 

请注意添加 row_number() over(partition by id order by lab_date desc) rn - 这会按降序日期顺序建立一个递增计数器(最新记录 = 1，较早记录 = 2 ... ) 对于每个不同的 id 从 1 重新开始。我们也加入它然后说 where rn = 1 只为每个样本类型选择最新记录