sql-server - 最大只能为空值，并将其他SQL Server分组(Max only null value and group the others SQL Server)

Question

I have a table like this:

(我有一张这样的桌子：)

Department  |Jan |Feb |Mar | Apr| 
+-----------+----+----+----+----+
|A          | 1  |NULL|NULL|NULL|    
|A          |NULL|NULL|2   |NULL|    
|A          |NULL|NULL|NULL|7   |    
|B          | 1  |NULL|2   |NULL|    
|B          |NULL|NULL|5   |NULL|    
|B          |NULL|6   |NULL|NULL| 

I want the output will be:

(我希望输出将是：)

+-----------+----+----+----+----+
|Department |Jan |Feb |Mar | Apr| 
+-----------+----+----+----+----+
|A          | 1  |NULL|2   |7   |    
|B          | 1  |6   |2   |NULL|    
|B          |NULL|NULL|5   |NULL|    

Does someone have an idea how to do this?

(有人知道如何执行此操作吗？)

Actually I want to do max only to the null values, and group by to the rest..

(实际上，我只想对空值进行max运算，然后对其余部分进行分组。)

  ask by Tehila translate from so

Answer 1

If the order does not really matter (as you say in your comment) then you can do it with ROW_NUMBER() window function:

(如果顺序并不重要（如您在评论中所述），则可以使用ROW_NUMBER()窗口函数进行操作：)

with cte as (
  select *,
    row_number() over (partition by [Department] order by [Jan]) rn1,
    row_number() over (partition by [Department] order by [Feb]) rn2,
    row_number() over (partition by [Department] order by [Mar]) rn3,
    row_number() over (partition by [Department] order by [Apr]) rn4
  from tablename
)
select c1.[Department], c1.[Jan], c2.[Feb], c3.[Mar], c4.[Apr]
from cte c1
inner join cte c2 on c2.[Department] = c1.[Department] and c2.rn2 = c1.rn1
inner join cte c3 on c3.[Department] = c1.[Department] and c3.rn3 = c1.rn1
inner join cte c4 on c4.[Department] = c1.[Department] and c4.rn4 = c1.rn1
where coalesce(c1.[Jan], c2.[Feb], c3.[Mar], c4.[Apr]) is not null
order by c1.[Department], c1.[Jan] desc, c2.[Feb] desc, c3.[Mar] desc, c4.[Apr] desc

See the demo .

(参见演示 。)
Results:

(结果：)

> Department |  Jan |  Feb | Mar |  Apr
> :--------- | ---: | ---: | --: | ---:
> A          |    1 | null |   2 |    7
> B          |    1 |    6 |   5 | null
> B          | null | null |   2 | null