Sql Server 查询多行并一行

干货

 

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;