生成2列而不是每组2行

Question

嘿我有这张我做的桌子..。

 Pat_Name        Case_Num         Doc_Entry_Date            Doc_id          Doc_Name          Doc_Order_By_Case            
 ------------------------------------------------------------------------------------------------------
 Jane            0943085          2022-07-09 10:00          3421             Said             1
 Jane            0943085          2022-07-09 11:00          8998             Juan             2
 Jane            0943085          2022-07-09 12:00          2429             Danielle         3
 Ben             0945666          2022-07-10 09:34          8998             Juan             1
 Ben             0945666          2022-07-10 09:38          7651             Ari              2
 Harry           0945331          2022-07-10 10:00          3421             Said             1
 Harry           0945331          2022-07-10 11:00          7651             Ari              2
 Hili            0945488          2022-07-10 18:00          6566             Vera             1
 Karen           0945112          2022-07-11 01:00          6566             Vera             1

我想要的是每个Case_Num用第一Doc列的一行，第二Doc的另一列(根据时间)

我已经完成了Doc_Order_By_Case列，以便更容易地使用以下内容：

 ROW_NUMBER() OVER (PARTITION BY Case_Num ORDER BY Doc_Entry_Date) AS Doc_Order_By_Case

有超过3份文件的案件我不在乎第三份。只有前两个文档

1 Doc的情况我希望第二个Doc的列为NULL

 Pat_Name    Case_Num     First_Doc_Entry_Date      First_Doc_Id    First_Doc_Name   Second_Doc_Entry_Date    Second_Doc_Id     Second_Doc_Name
 -------------------------------------------------------------------------------------------------------------------------------------------
 Jane        0943085      2022-07-09 10:00          3421            Said             2022-07-09 11:00         8998               Juan
 Ben         0945666      2022-07-10 09:34          8998            Juan             2022-07-10 09:38         7651               Ari
 Harry       0945331      2022-07-10 10:00          3421            Said             2022-07-10 11:00         7651               Ari 
 Hili        0945488      2022-07-10 18:00          6566            Vera              NULL                    NULL               NULL
 Karen       0945112      2022-07-11 01:00          6566            Vera              NULL                    NULL               NULL

你能帮我查一下密码吗？非常感谢!

Answer 1

你可以用两种方法来做。

1. 通过Pat_name, case_num将数据组向上转换，并将值分别聚合到Doc_Order_By_Case列为1/2的地方。

代码：

select Pat_Name,case_num,
max(case when Doc_Order_By_Case = 1 then Doc_Entry_Date else null end ) as First_Doc_Entry_Date,
max(case when Doc_Order_By_Case = 1 then Doc_id else null end ) as First_Doc_Id,
max(case when Doc_Order_By_Case = 1 then Doc_Name else null end ) as First_Doc_Name,

max(case when Doc_Order_By_Case = 2 then Doc_Entry_Date else null end ) as Second_Doc_Entry_Date,
max(case when Doc_Order_By_Case = 2 then Doc_id else null end ) as Second_Doc_Id,
max(case when Doc_Order_By_Case = 2 then Doc_Name else null end ) as Second_Doc_Name 
from Table1 group by Pat_Name,case_num

1. 选择Doc_Order_By_Case标志为1的所有记录，并将其与Doc_Order_By_Case为2的记录连接起来

代码：

select 
  a.pat_name, 
  a.case_num, 
  a.Doc_Entry_Date as First_Doc_Entry_Date, 
  a.Doc_id as First_Doc_Id, 
  a.Doc_Name as First_Doc_Name, 
  b.Doc_Entry_Date as Second_Doc_Entry_Date, 
  b.Doc_id as Second_Doc_Id, 
  b.Doc_Name as Second_Doc_Name 
from 
  (
    select Pat_Name, case_num, Doc_Entry_Date, Doc_id, Doc_Name 
    from Table1 
    where Doc_Order_By_Case = 1
  ) a 
  left join (
    select Pat_Name, case_num, Doc_Entry_Date, Doc_id, Doc_Name 
    from Table1 
    where Doc_Order_By_Case = 2
  ) b on a.case_num = b.case_num

输出：

​

Sql Fiddle

Answer 2

尝尝这个

;with LimitedRecords as (
    select *
    from (
             select *, ROW_NUMBER() OVER (PARTITION BY Case_Num ORDER BY Doc_Entry_Date) AS Doc_Order_By_Case
             from @DATA
         ) AGG
    where Doc_Order_By_Case <= 2
)
select 
       L1.Doc_Order_By_Case, L2.Doc_Order_By_Case, L1.Pat_Name, L1.Case_Num,
       L1.Doc_Entry_Date as First_Doc_Entry_Date, L1.Doc_Id as First_Doc_Id, L1.Doc_Name as First_Doc_Name,
       L2.Doc_Entry_Date as Second_Doc_Entry_Date, L2.Doc_Id as Second_Doc_Id, L2.Doc_Name as Second_Doc_Name
from LimitedRecords L1 left join LimitedRecords L2 on L1.Pat_Name = L2.Pat_Name and L1.Doc_Order_By_Case < L2.Doc_Order_By_Case
where L1.Doc_Order_By_Case = 1