SQL Server : how to merge or group up rows?-CodePudding

In T-SQL, given input data such as

 ------ ------ -------- ------ ------ ------ -------- ------ 
| Col1 | Col2 |  Col3  | Col4 | Col5 | Col6 |  Col7  | Col8 |
 ------ ------ -------- ------ ------ ------ -------- ------ 
| 1    | 30   | 1.0000 | desc | NULL | NULL | NULL   | NULL |
| 31   | 60   | 2.0000 | desc | NULL | NULL | NULL   | NULL |
| 61   | 90   | 1.0000 | desc | NULL | NULL | NULL   | NULL |
| NULL | NULL | NULL   | NULL | 1    | 30   | 1.5000 | desc |
| NULL | NULL | NULL   | NULL | 1    | 30   | 2.5000 | desc |
| NULL | NULL | NULL   | NULL | 1    | 30   | 1.1000 | desc |
 ------ ------ -------- ------ ------ ------ -------- ------

How can I obtain this output:

 ------ ------ -------- ------ ------ ------ -------- ------ 
| Col1 | Col2 |  Col3  | Col4 | Col5 | Col6 |  Col7  | Col8 |
 ------ ------ -------- ------ ------ ------ -------- ------ 
|    1 |   30 | 1.0000 | desc |    1 |   30 | 1.5000 | desc |
|   31 |   60 | 2.0000 | desc |    1 |   30 | 2.5000 | desc |
|   61 |   90 | 1.0000 | desc |    1 |   30 | 1.1000 | desc |
 ------ ------ -------- ------ ------ ------ -------- ------

Rows 4, 5 and 6 from input "merge up" in order to get the desired output.

This should also work in case the total number of rows is not even.

CodePudding user response：

Here is a solution. This doesn't work if the right half of the given table has more rows than the left. You can see what I'm doing and you can modify it to handle that case:

DECLARE @temp1 TABLE ( col1 INT, col2 INT, col3 DECIMAL(10,4), col4 NVARCHAR(20), col5 INT, col6 INT, col7 DECIMAL(10,4), col8 NVARCHAR(20) )

INSERT INTO @temp1 (col1,col2,col3,col4) VALUES
(1,30,1,'desc'),
(31,60,1,'desc'),
(61,90,1,'desc'),
(81,120,1,'desc')

INSERT INTO @temp1 (col5,col6,col7,col8) VALUES
(1,30,1.5,'desc'),
(1,30,2.5,'desc'),
(1,30,1.1,'desc')

SELECT col1,col2,col3,col4,col5,col6,col7,col8 FROM
(
    SELECT col1,col2,col3,col4,ROW_NUMBER() OVER(ORDER BY col5) AS RowNumber  FROM @temp1
    WHERE col1 IS NOT NULL
) t1 LEFT JOIN 
(
    SELECT col5,col6,col7,col8,ROW_NUMBER() OVER(ORDER BY col1) AS RowNumber  FROM @temp1
    WHERE col5 IS NOT NULL
) t2 ON t1.RowNumber = t2.RowNumber

Results: