合并具有空值的行，而不使用 group by。

Question

假设我有一个SELECT语句，返回以下数据：

select nr, name, val_1, val_2, val_3 
from table

 Nr. | Name       | Value 1 | Value 2 | Value 3
-----+------------+---------+---------+---------
   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

我想将行压缩到最少的内容。

我想要以下结果：

 Nr. | Name       | Value 1 | Value 2 | Value 3
-----+------------+---------+---------+---------
   1 | Max        | 123     | 456     | 789
   9 | Lisa       | 1       | NULL    | Hello
   9 | Lisa       | 3       | NULL    | NULL
   9 | Lisa       | 9       | NULL    | NULL

为了将最大值（编号1）的行压缩成一行，使用最大值的分组将会很有帮助。

select nr, name, max(val_1), max(val_2), max(val_3) 
from table 
group by nr, name 

但是我不确定如何为Lisa（编号9）获得所需的结果。Lisa的行包含Value 3列中的一个值，在此示例中，它与具有Null值的Nr和Name匹配的第一行合并。

非常感谢您的每一个输入！

- lechnerio

Answer 1

基本原则与Vladimir的解决方案相同。这个解决方案使用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

- Squirrel

Answer 2

这是一种实现方式。通过按照将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  |
+----+------+-----+------+-------+

- Vladimir Baranov

---

嗨，弗拉基米尔，这非常有帮助。非常感谢！ - lechnerio