clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36287 in the last week).

CREATE TABLE YourTable ([ID] varchar(3), [Cases] varchar(5), [Time1] int, [Time2] int, [Time3] int, [Time4] int, [Time5] int, [Time6] int, [Time7] int, [Time8] int, [Time9] int) ; INSERT INTO YourTable ([ID], [Cases], [Time1], [Time2], [Time3], [Time4], [Time5], [Time6], [Time7], [Time8], [Time9]) VALUES ('ID1', 'Case1', 8, 0, 10, 9, 8, 7, 6, 5, 4), ('ID1', 'Case2', 2, 1, 0, 0, 8, 4, 2, 1, 0), ('ID1', 'Case3', 5, 4, 0, 7, 6, 5, 4, 0, 7), ('ID1', 'Case4', 0, 5, 0, 5, 0, 5, 0, 5, 0), ('ID1', 'Case5', 4, 0, 0, 9, 8, 7, 6, 5, 4), ('ID1', 'Case6', 0, 6, 0, 0, 6, 0, 0, 6, 0) ; 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
ID Time3 Cases3 Time4 Cases4 Time5 Cases5 Time6 Cases6 Time7 Cases7 Time8 Cases8 Time9 Cases9
ID1 10 Case1 9 Case5 8 Case2 5 Case4 0 Case6 6 Case6 7 Case3
Warning: Null value is eliminated by an aggregate or other SET operation.
 hidden batch(es)