0

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..

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tehila
  • 61
  • 2
  • 7
  • Your desire output might be something wrong what is wrong with 5 in march and why it's in last row? – Hardik Masalawala Dec 01 '19 at 09:24
  • Please explain how you reach your desired output? – Farhad Rahmanifard Dec 01 '19 at 10:05
  • I didnt get this output , but I want this output. This is actualy the question. How I reach this output. and about the value 5 in march its because I want to merge all the rows with same department only if there the other rows with null value, else I want it in different rows – Tehila Dec 01 '19 at 10:17
  • What would be your expected output if there was another row: `|B |NULL|NULL|3 |NULL|`? – forpas Dec 01 '19 at 10:20
  • forpas, Thanks for your help, Then in case of this row then I want this row in the output as separate row. becaous it different value in the same month – Tehila Dec 01 '19 at 10:27
  • And it is not really matter the order of the rows.. I mean it's not matter if it will be |B |1 |6 |2 |NULL or |B |1 |6| 5 |NULL and the the other row... – Tehila Dec 01 '19 at 10:33

1 Answers1

0

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

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
forpas
  • 160,666
  • 10
  • 38
  • 76