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

Time:10-02

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 -> enter image description here

Output ->enter image description here

CodePudding user response:

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;
  • Related