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.

I adopted a script from a link below, but it seems to work with known rows. Can it be done for an unknown rows (like SELECT * does)? I mean i have multiple tables, and I really don't want to use dynamic SQL or write tens of usp to implement MergeTable1, MergeTable2 and so on.

My try (doesn't work as expected):

WITH CTE AS
(
  SELECT id, A, B, C, relevantid,
    MAX(relevantid) OVER( ORDER BY id
                          ROWS UNBOUNDED PRECEDING ) AS grp
  FROM [Test].[dbo].[T]
    CROSS APPLY(VALUES(CASE WHEN [A] IS NOT NULL OR [B] IS NOT NULL OR [C] IS NOT NULL THEN id END)) 
      AS A(relevantid)

),
CTE2 AS
(
    SELECT id,

         MAX(A) OVER( PARTITION BY grp
                      ORDER BY id
                      ROWS UNBOUNDED PRECEDING ) A,
         MAX(B) OVER( PARTITION BY grp
                    ORDER BY id
                    ROWS UNBOUNDED PRECEDING ) B,

         MAX(C) OVER( PARTITION BY grp
                      ORDER BY id
                      ROWS UNBOUNDED PRECEDING) C

    FROM CTE
),
CTE3 AS
(
    SELECT *, row_number() OVER (PARTITION BY id ORDER BY (SELECT 0)) Priority
    FROM CTE2
),
CTE4 AS
(
    SELECT *, MAX(Priority) OVER (PARTITION BY id ORDER BY (SELECT 0)) MaxPriority
    FROM CTE3
)

SELECT *
FROM CTE4
WHERE Priority = MaxPriority

Current script for a table:

CREATE TABLE [dbo].[T](
    [id] [int] NOT NULL,
    [A] [int] NULL,
    [B] [int] NULL,
    [C] [int] NULL,
    [Priority] [int] NOT NULL
) ON [PRIMARY]

GO
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20160204-122857] ON [dbo].[T]
(
    [id] ASC,
    [Priority] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
INSERT [dbo].[T] ([id], [A], [B], [C], [Priority]) VALUES (1, NULL, 3, 4, 1)
GO
INSERT [dbo].[T] ([id], [A], [B], [C], [Priority]) VALUES (1, 5, 6, NULL, 2)
GO
INSERT [dbo].[T] ([id], [A], [B], [C], [Priority]) VALUES (1, 8, NULL, NULL, 3)
GO
INSERT [dbo].[T] ([id], [A], [B], [C], [Priority]) VALUES (2, 634, 346, 359, 1)
GO
INSERT [dbo].[T] ([id], [A], [B], [C], [Priority]) VALUES (2, 34, NULL, 734, 2)
GO

desired result:

id  A   B   C   
1   8   6   4   
2   34  346 734