Merging rows with same ID on non-null fields

[FernandaNava]

I have a table in which the an ID appears several times, but for each, there's only one field that is not null: 

ID	Stage1Date	Stage2Date	Stage3Date
1	01-01-2020	-	-
1	-	01-02-2020	-
1	-	-	01-03-2020

Is there a way in which I could merge these into a single row?

PS.  I built this table from another one that looked like this:

ID	ChangeDate	InitialStage	FinalStage
1	01/02/2020	1	2
1	01/03/2020	2	3

Therefore, if this could be solved more easily by changing the way the table is constructed I could do it too.

Thank you 

[klikgevoel · Accepted solution]

If I understand you correctly you want one row with the Stage*Date variables, i.e.

ID	Stage1Date	Stage2Date	Stage3Date
1	01/01/2020	01/02/2020	01/03/2020

This can be achieved via Left Join prefix and Where clause.

Example

Data:

LOAD

ID,

Stage1Date

From DataSource

Where Not IsNull(Stage1Date);

Left Join(Data)

LOAD

ID,

Stage2Date

From DataSource

Where Not IsNull(Stage2Date);

etc.

See attachment for little explanation and example.