基本原理与弗拉基米尔的解法相同，这里使用UNPIVOT和PIVOT

with cte as
(
    select nr, name, col, val,
           rn = row_number() over(partition by nr, name, col order by val) 
    from   [table]
           unpivot
           (
                val
                for col in (val_1, val_2, val_3)
           ) u
)
select *
from   (
           select nr, name, rn, col, val
           from   cte
       ) d
       pivot
       (
           max (val)
           for col in ([val_1], [val_2], [val_3])
       ) p

有一种方法可以做到这一点：为每一列分配一个唯一的行号，方法是按照NULL排在最后的方式对它们进行排序，然后使用这些行号将它们重新连接在一起，并删除所有为NULL的行。
首先只运行CTE，然后检查中间结果以了解其工作原理。

样本数据

DECLARE @T TABLE (Nr varchar(10), Name varchar(10), V1 varchar(10), V2 varchar(10), V3 varchar(10));
INSERT INTO @T VALUES
('1', 'Max ', '123'  , NULL , NULL ),
('1', 'Max ', NULL   , '456', NULL ),
('1', 'Max ', NULL   , NULL , '789'),
('9', 'Lisa', '1'    , NULL , NULL ),
('9', 'Lisa', '3'    , NULL , NULL ),
('9', 'Lisa', NULL   , NULL , 'Hello'),
('9', 'Lisa', '9'    , NULL , NULL );

查询

WITH CTE
AS
(
    SELECT
        Nr
        ,Name
        ,V1
        ,V2
        ,V3
        -- here we use CASE WHEN V1 IS NULL THEN 1 ELSE 0 END to put NULLs last
        ,ROW_NUMBER() OVER (PARTITION BY Nr ORDER BY CASE WHEN V1 IS NULL THEN 1 ELSE 0 END, V1) AS rn1
        ,ROW_NUMBER() OVER (PARTITION BY Nr ORDER BY CASE WHEN V2 IS NULL THEN 1 ELSE 0 END, V2) AS rn2
        ,ROW_NUMBER() OVER (PARTITION BY Nr ORDER BY CASE WHEN V3 IS NULL THEN 1 ELSE 0 END, V3) AS rn3
    FROM @T AS T
)
SELECT
    T1.Nr
    ,T1.Name
    ,T1.V1
    ,T2.V2
    ,T3.V3
FROM
    CTE AS T1
    INNER JOIN CTE AS T2 ON T2.Nr = T1.Nr AND T2.rn2 = T1.rn1
    INNER JOIN CTE AS T3 ON T3.Nr = T1.Nr AND T3.rn3 = T1.rn1
WHERE
    T1.V1 IS NOT NULL
    OR T2.V2 IS NOT NULL
    OR T3.V3 IS NOT NULL
ORDER BY
    T1.Nr, T1.rn1
;

结果

+----+------+-----+------+-------+
| Nr | Name | V1  |  V2  |  V3   |
+----+------+-----+------+-------+
|  1 | Max  | 123 | 456  | 789   |
|  9 | Lisa |   1 | NULL | Hello |
|  9 | Lisa |   3 | NULL | NULL  |
|  9 | Lisa |   9 | NULL | NULL  |
+----+------+-----+------+-------+