How can I detect the maximum value in several rows and columns at the same time in an SQL table? I have a table which records values over time, similar to the following:



The next value after the zero value indicates the beginning of the highest value to one case for a given time.
Example: To Time6 the maximum value is 5 because only case4 Start (after zero value) in that moment the others continuing decreasing at that time.

I need to know at a given moment what is the maximum value case for all the times shown and gradually place it as shown in the following table:



I have worked with some of the aggregate and analytic functions, but can't seem to find the correct solution especially because query is to a table of 40 million records. 
I hope an SQL expert guru can help me out, or at least point me in the right direction or some efficient algorithm. Is there a way to query this (preferably without a cursor?)

Thanks in advance.
If I understood what you are trying to do correctly then this should work (fiddle).

The CASE expression ensures that only values with 0 in the preceding column are considered for the aggregation. Prior to the aggregation the integers (presumed >= 0) are padded out with leading zeroes to make them exactly 10 characters long and concatenated to the Cases string.

The MAX is then found to get the lexicographically greatest concatenated string for that time (as here) and then this is split into its constituent parts.

In the event that there is more than one eligible row with the same maximum value for a specific time this will somwehat arbitrarily pick the one with the alphabetically latest Cases string

WITH Grouped As
(
SELECT [ID], 
      MAX(CASE WHEN [Time2] = 0 THEN FORMAT([Time3],'D10') + [Cases] END) AS TC3, 
      MAX(CASE WHEN [Time3] = 0 THEN FORMAT([Time4],'D10') + [Cases] END) AS TC4, 
      MAX(CASE WHEN [Time4] = 0 THEN FORMAT([Time5],'D10') + [Cases] END) AS TC5, 
      MAX(CASE WHEN [Time5] = 0 THEN FORMAT([Time6],'D10') + [Cases] END) AS TC6, 
      MAX(CASE WHEN [Time6] = 0 THEN FORMAT([Time7],'D10') + [Cases] END) AS TC7, 
      MAX(CASE WHEN [Time7] = 0 THEN FORMAT([Time8],'D10') + [Cases] END) AS TC8, 
      MAX(CASE WHEN [Time8] = 0 THEN FORMAT([Time9],'D10') + [Cases] END) AS TC9
FROM YourTable
GROUP BY [ID]
)
SELECT [ID], 
       Time3 = 0+LEFT(TC3,10),
       Cases3 = SUBSTRING(TC3,11,8000),
       Time4 = 0+LEFT(TC4,10),
       Cases4 = SUBSTRING(TC4,11,8000),
       Time5 = 0+LEFT(TC5,10),
       Cases5 = SUBSTRING(TC5,11,8000),
       Time6 = 0+LEFT(TC6,10),
       Cases6 = SUBSTRING(TC6,11,8000),
       Time7 = 0+LEFT(TC7,10),
       Cases7 = SUBSTRING(TC7,11,8000),
       Time8 = 0+LEFT(TC8,10),
       Cases8 = SUBSTRING(TC8,11,8000),
       Time9 = 0+LEFT(TC9,10),
       Cases9 = SUBSTRING(TC9,11,8000)
FROM Grouped