干货
CREATE TABLE #benefit_code21 (id INT, number nvarchar(MAX), pname nvarchar(255), collegeID INT, applicationdate datetime, authorizedate datetime, lawState int, lawTime datetime, country nvarchar(255), category nvarchar(255), mainCategoryNumber nvarchar(255), allCategoryNumber nvarchar(255), typess int, levelss int, Introduction nvarchar(MAX), AgencyID int, Agent nvarchar(255), Remark nvarchar(max), EName nvarchar(255), Paname nvarchar(255), Cont nvarchar(255), Depname nvarchar(255), name nvarchar(255)); insert into #benefit_code21(id, number, pname, collegeID, applicationdate, authorizedate, lawState, lawTime, country, category, mainCategoryNumber ,allCategoryNumber, typess, levelss, Introduction, AgencyID, Agent ,Remark ,EName ,Paname ,Cont ,Depname,name) SELECT Patent.ID, Patent.Number, Patent.Name, Patent.CollegeID , Patent.ApplicationDateTime , Patent.AuthorizeDateTime , Patent.LawState , Patent.LawStateTime , Patent.Country , Patent.Category , Patent.MainCategoryNumber , Patent.AllCategoryNumber , Patent.Type , Patent.Level , Patent.Introduction , Patent.AgencyID , Patent.Agent , Patent.Remark ,Expert.Name ,PatentAgency.Name, PatentAgency.Contract, Department.Name, STUFF( ( SELECT ';' + CAST(name AS VARCHAR(MAX)) FROM dbo.PatentInventer WHERE (Patent.ID = PatentInventer.PatentID) FOR XML PATH ('') ) ,1,1,'') AS NameValues FROM dbo.PatentInventer ,dbo.Patent,Expert,PatentAgency,Department where Patent.ID = PatentInventer.PatentID and PatentAgency.ID=patent.AgencyID and patent.CollegeID=Department.ID and PatentInventer.ExpertID = Expert.ID and PatentInventer.IsPrincipal = 'True' GROUP BY Patent.ID, Patent.Number, Patent.Name, Patent.CollegeID , Patent.ApplicationDateTime , Patent.AuthorizeDateTime , Patent.LawState , Patent.LawStateTime , Patent.Country , Patent.Category , Patent.MainCategoryNumber , Patent.AllCategoryNumber , Patent.Type , Patent.Level , Patent.Introduction , Patent.AgencyID , Patent.Agent , Patent.Remark ,Expert.Name ,PatentAgency.Name, PatentAgency.Contract, Department.Name select * from #benefit_code21; drop table #benefit_code21;