I am currently trying to query a table such as this where the phases of a project and the dates of those phases are all in the same columns. The table is as follows:

I am hoping to query the table to get a list of each project with it's date approved and closed. If the project has not reached the approve phase yet, then it will not show in the table. If it has reached approve or implement but not close yet, it will show in the table the approve date and Null for the close date such as this:

I am trying to left join the table to itself, but when I do this I get duplicate rows of the project ID where the approve date is there and the closed date is null, then the approve date is null but the close date is there. Any help would be greatly appreciated!

CodePudding user response：

You can use conditional aggregation. For example:

select
  project_id,
  max(case when phase = 'Approve' then date end) as approve,
  max(case when phase = 'Close' then date end) as close,
from t
group by project_id

CodePudding user response：

Here's a solution that provides all the info while eliminating cases where Approve is null

select   [Project ID]
        ,max(case Phase when 'Approve' then [Date] end) as  Approve
        ,max(case Phase when 'Close'   then [Date] end) as  [Close]
from     t
group by [Project ID]
having   max(case Phase when 'Approve' then [Date] end) is not null

Fiddle