sql - SQL扩展表以合并具有重复数据的字段

Question

我有一个包含一些重复数据的表，我想将字段合并到一个表中。

我的数据目前如下所示：

name       job1        Unit1_Level1       Unit1__Level2
-------------------------------------------------------
A          Teacher     Infomation      Information_office
A          Staff       Secretary       Secretary_office
B          Teacher     Finacial        Finacial_office
C          Teacher     Engineer        Engineer_office
C          Staff       Library         Library_office
D          Staff       Library         Library_office    

我希望它看起来像这样：

name       job1        Unit1_Level1       Unit1__Level2       job2     Unit2_Level1     Unit2__Level2
------------------------------------------------------------------------------------------------------
A          Teacher     Infomation      Information_office     Staff    Secretary        Secretary_office
B          Teacher     Finacial        Finacial_office
C          Teacher     Engineer        Engineer_office        Staff    Library          Library_office
D          Staff       Library         Library_office    

希望有人能帮帮我，谢谢！

标签： sqlsql-server

Answer 1

您可以使用 row_number() 函数来计算同一个人的每次迭代（工作）。使用该数字，您可以返回他们第一次迭代的选择，并加入他们的第二次迭代......甚至加入第三次，第四次......迭代。

with cte as (
  select name, job1, Unit1_Level1, Unit1_Level2,
         row_number() over (partition by name order by job1 desc) as iteration
  from MyTable
)
select i1.name, i1.job1, i1.Unit1_Level1, i1.Unit1_Level2,
       i2.job1 as job2, i2.Unit1_Level1 as Unit2_Level1, i2.Unit1_Level2 as Unit2_Level2,
       i3.job1 as job3, i3.Unit1_Level1 as Unit3_Level1, i3.Unit1_Level2 as Unit3_Level2
from cte as i1
     left join cte as i2 on i2.name = i1.name and i2.iteration = 2
     left join cte as i3 on i3.name = i1.name and i3.iteration = 3
where i1.iteration = 1

PS：您没有指明要按什么顺序返回同一个人的工作，所以我使用job1 desc来返回它们作为您的样本数据：第一个教师和第二个工作人员。