-2

I've got a PIVOT table output in SQL Server Management Studio. Every row contains data that are either filled or NULL. I need to fill the NULL values according to the following logic:

  1. If value is NULL, then go further to the left (in the row) and fill it with the closest value to the left.
  2. If there are no values to the left, then use the closest value to the right to fill it.

enter image description here

Thanks for your kind help, Dave

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
Dave
  • 97
  • 1
  • 8
  • Sample data as actual data, not [images](https://meta.stackoverflow.com/questions/285551) of data and your current query as a [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) and your attempt will be useful. – Stu Oct 05 '22 at 13:41

1 Answers1

3

Since you have a limited number of columns, coalesce() should do the trick

Select Product
      ,Time1  = coalesce(Time1,Time2,Time3,Time4,Time5,Time10,Time15,Time20,Time30)
      ,Time2  = coalesce(Time2,Time3,Time4,Time5,Time10,Time15,Time20,Time30,Time1)
      ,Time3  = coalesce(Time3,Time4,Time5,Time10,Time15,Time20,Time30,Time1,Time2)
      ,Time4  = coalesce(Time4,Time5,Time10,Time15,Time20,Time30,Time1,Time2,Time3)
      ,Time5  = coalesce(Time5,Time10,Time15,Time20,Time30,Time1,Time2,Time3,Time4)
      ,Time10 = coalesce(Time10,Time15,Time20,Time30,Time1,Time2,Time3,Time4,Time5)
      ,Time15 = coalesce(Time15,Time20,Time30,Time1,Time2,Time3,Time4,Time5,Time10)
      ,Time20 = coalesce(Time20,Time30,Time1,Time2,Time3,Time4,Time5,Time10,Time15)
      ,Time30 = coalesce(Time30,Time1,Time2,Time3,Time4,Time5,Time10,Time15,Time20)
 From ...
 Pivot ...
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • Looks great, exactly what I was looking for! To incorporate the logic, I switched the order, e.g. Time15 = coalesce(Time15,Time10,Time5,Time4,Time3,Time2,Time1,Time20,Time30) – Dave Oct 05 '22 at 14:50
  • @Dave I figured it may require a tweak or two :) Just wanted to illustrate coalesce – John Cappelletti Oct 05 '22 at 14:52