Combine join results in single row-CodePudding

I am joining two tables of project details on the project table. The project table does not contain duplicate entries for a project. The table "ProjectDeadLines" may contain two separate rows for a project as project start and end are recorded separately.

Snippet of project table:

ID	name	sum
111	Test Project1	100
111	Test Project2	200

Snippet of project deadline table:

ID	type	date
111	start	2022-03-01
111	end	2022-03-04

The desired output would be:

ID	name	sum	start	end
111	Test Project	100	2022-03-01	2022-03-04

Table "ProjectSubmission" contains the value "sum" in the column "PSU_NetAmount". Table "ProjectDeadLines" contains the values "date" and "type" in the columns "PDL_Date" and "PDL_Name".

The current statement joins everything as desired, but creates two rows for each project if a start and end date are defined.

SELECT PRO_ProjectNumber, PRO_Description1, PSU_NetAmount, PDL_Date, PDL_Name
FROM Project
         JOIN ProjectSubmission PS on Project.PRO_PK = PS.PSU_PRO_FK
         JOIN ProjectDeadLines PDL on Project.PRO_PK = PDL.PDL_PRO_FK;

Is there any simple way to achieve this desired output with MS-SQL?

CodePudding user response：

use aggregation

SELECT PRO_ProjectNumber, PRO_Description1,sum(PSU_NetAmount), 
min(case when type='start' then PDL_Date end) start_date, 
 max(case when type='end' then PDL_Date end) as end_date,
 PDL_Name
FROM Project
JOIN ProjectSubmission PS on Project.PRO_PK = PS.PSU_PRO_FK
 group by PRO_ProjectNumber, PRO_Description1

CodePudding user response：

You can do this:

SELECT PRO_ProjectNumber, PRO_Description1, PSU_NetAmount, PDLs.PDL_Date, PDLe.PDL_Date
FROM Project
         JOIN ProjectSubmission PS on Project.PRO_PK = PS.PSU_PRO_FK
         JOIN ProjectDeadLines PDLs on Project.PRO_PK = PDL.PDL_PRO_FK AND type='start'
         JOIN ProjectDeadLines PDLe on Project.PRO_PK = PDL.PDL_PRO_FK AND type='end'
;