求多行子表信息转成主表信息的列

[泥泞的我]

主表简历表resumeGid,子表教育经历表 ，子表中每个人对应4条教育经历，需要将教育经历的列变成主表的列，建表语句如下

CREATE TABLE [dbo].[ResumeEducationHistory](
	[EduGID] [uniqueidentifier] NOT NULL,
	[ResumeGID] [uniqueidentifier] NOT NULL,
	[CollegeCode] [varchar](100) NULL,
	[CollegeName] [nvarchar](200) NULL,
	[Faculty] [nvarchar](100) NULL,
	[MajorCode] [varchar](100) NULL,
	[MajorName] [nvarchar](200) NULL,
	[StartDate] [date] NULL,
	[EndDate] [date] NULL,
	[IsHitherto] [bit] NULL,
	[EducationLevelCode] [varchar](100) NULL,
	[EducationLevelName] [nvarchar](200) NULL,
	[DegreeCode] [varchar](100) NULL,
	[DegreeName] [nvarchar](200) NULL,
	[EducationalSystem] [nvarchar](50) NULL,
	[StudyMode] [nvarchar](50) NULL,
	[TrainingStyle] [nvarchar](50) NULL,
	[GPA] [decimal](18, 2) NULL,
	[MajorRanking] [nvarchar](50) NULL,
	[MajorRemark] [nvarchar](1000) NULL,
	[SequenceNum] [int] NOT NULL,
 CONSTRAINT [PK__ResumeEd__D403ABC3A3208640] PRIMARY KEY CLUSTERED 
(
	[EduGID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ResumeEducationHistory]  WITH CHECK ADD  CONSTRAINT [FK_ResumeEducationHistory_Resume] FOREIGN KEY([ResumeGID])
REFERENCES [dbo].[Resume] ([ResumeGID])
GO

ALTER TABLE [dbo].[ResumeEducationHistory] CHECK CONSTRAINT [FK_ResumeEducationHistory_Resume]
GO

 
CREATE TABLE [dbo].[Resume](
	[ResumeGID] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	 
 CONSTRAINT [PK_RESUME] PRIMARY KEY CLUSTERED 
(
	[ResumeGID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

[卖水果的net]

测试数据也给出一些来。

[RINK_1]

select A.ResumeGID,A.Name,
max(case when rn=1 then StartDate else null end) as StartDate_1,
max(case when rn=1 then EndDate else null end) as EndDate_1,
max(case when rn=1 then CollegeName else null end) as CollegeName_1,
max(case when rn=1 then DegreeName else null end) as DegreeName_1,
max(case when rn=1 then Faculty else null end) as Faculty_1,
max(case when rn=1 then MajorName else null end) as MajorName_1,
max(case when rn=1 then MajorRanking else null end) as MajorRanking_1,
max(case when rn=2 then StartDate else null end) as StartDate_2,
max(case when rn=2 then EndDate else null end) as EndDate_2,
max(case when rn=2 then CollegeName else null end) as CollegeName_2,
max(case when rn=2 then DegreeName else null end) as DegreeName_2,
max(case when rn=2 then Faculty else null end) as Faculty_2,
max(case when rn=2 then MajorName else null end) as MajorName_2,
max(case when rn=2 then MajorRanking else null end) as MajorRanking_2,
max(case when rn=3 then StartDate else null end) as StartDate_3,
max(case when rn=3 then EndDate else null end) as EndDate_3,
max(case when rn=3 then CollegeName else null end) as CollegeName_3,
max(case when rn=3 then DegreeName else null end) as DegreeName_3,
max(case when rn=3 then Faculty else null end) as Faculty_3,
max(case when rn=3 then MajorName else null end) as MajorName_3,
max(case when rn=3 then MajorRanking else null end) as MajorRanking_3,
max(case when rn=4 then StartDate else null end) as StartDate_4,
max(case when rn=4 then EndDate else null end) as EndDate_4,
max(case when rn=4 then CollegeName else null end) as CollegeName_4,
max(case when rn=4 then DegreeName else null end) as DegreeName_4,
max(case when rn=4 then Faculty else null end) as Faculty_4,
max(case when rn=4 then MajorName else null end) as MajorName_2,
max(case when rn=4 then MajorRanking else null end) as MajorRanking_2,
from Resume A
left join 
(select *,row_number() over (partition by ResumeGID order by StartDate) as rn from ResumeEducationHistory) as B on A.ResumeGID=B.ResumeGID
group by A.ResumeGID,A.Name