Way to merge non-null columns of a SQL table based on an ID

I have a scenario where I need to have all the non-null valued columns of a SQL table to be merged in a way that the output all all the fields populated with the last non-null value.

Input ->

Output ->

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Aggregate by ID and select the MAX value of each column:

SELECT ID, MAX(ColA) AS ColA, MAX(ColB) AS ColB, MAX(ColC) AS ColC
FROM yourTable
GROUP BY ID;

Edit: For a bit column in SQL Server, you may cast to integer before taking the max:

SELECT
    ID,
    MAX(CAST(ColA AS int)) AS ColA,
    MAX(CAST(ColB AS int)) AS ColB,
    MAX(CAST(ColC AS int)) AS ColC
FROM yourTable
GROUP BY ID;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0