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