In my table I have rows, and each row has some data and its version. For example

    id col1 col2 col3 col4 priority
    1  A    NULL B    NULL 1
    1  NULL K    NULL NULL 2
    1  NULL C    D    NULL 3

the result should be:

    id col1 col2 col3 col4
    1  A    C    D    NULL 

what logic is used here? It's simple, when column is not-null, it overrides an old value otherwise nothing happens.

Pseudocode (in C#)

    var resultTable = new List<Row>();
    foreach (var group in table.GroupBy(x => x.ID).Select(g => g.OrderBy(x => x.Priority)))
    {
        var row = Row.GetEmpty();
        foreach (var anotherRow in group)
        {
            foreach (var column in group.Columns)
            {
                if (column.GetValue(anotherRow) != null)
                    column.SetValue(row, anotherRow);
            }
        }
        resultTable.Add(row);
    }

How could it be done with standard SQL features? I saw someone who suggested use SQLCLR, but I want to avoid it if possible.