declare @Sparse table 
	(	Id int,
		dt datetime,
		s_1 varchar(max) sparse null, 
		s_2 varchar(max) sparse null, 
		-- ...
		s_500 varchar(max) sparse null
	)
insert into @Sparse (Id, dt, s_1, s_2, s_500)
	values	(1, getdate(), 'John', null, null), 
		(1, getdate()-1, 'J', null, 'Kennedy'),
		(1, getdate()-2, null, 'F', 'Unknown'),
		--
		(2, getdate(), 'James', 'Earl', null), 
		(2, getdate()-1, 'James', null, null),
		(2, getdate()-2, null, 'n/a', 'Jones')

-- flatten dataset for each Id, using most recent value from each sparse column
-- desired:
/*
id    s_1    s_2    s_500
----- ------ ------ -----
1     John   F      Kennedy
2     James  Earl   Jones

*/

;with	
	c_Stage (id, diff, s_1, s_2, s_500)
	as	(	select	id, 
				cast(datediff(ms, dt, getdate()) as varchar(max)),
				cast(s_1 as varchar(max)), 
				cast(s_2 as varchar(max)), 
				cast(s_500 as varchar(max))
			from	@Sparse
		),
	 c_Sort (id, sort, s_1, s_2, s_500) 
	 as	(	select	id, 
				cast(row_number() over(order by replicate('0',len(diff)) desc, diff desc) as varchar(max)),
				s_1, 
				s_2, 
				s_500
			from	c_Stage
	 ),
	 c_Fin (id, s_1, s_2, s_500) 
	as	(	select	id, 
				sort+'.'+s_1,
				sort+'.'+s_2,
				sort+'.'+s_500
			from	c_Sort
		)
select	id, 
	[s_1] = stuff(max(s_1), 1, charindex('.', max(s_1), 0), ''),
	[s_2] = stuff(max(s_2), 1, charindex('.', max(s_2), 0), ''),
	[s_500] = stuff(max(s_500), 1, charindex('.', max(s_500), 0), '')
from	c_Fin
group
by	id;

;WITH cteSource(ID, RecID, theValue, theCol)
AS (
	SELECT		u.ID,
			ROW_NUMBER() OVER (PARTITION BY u.ID, u.theCol ORDER BY u.DT DESC) AS RecID,
			u.theValue,
			u.theCol
	FROM		@Sparse AS s
	UNPIVOT		(
				theValue
				FOR theCol IN (s.s_1, s.s_2, s.s_500)
			) AS u
)
SELECT	p.ID,
	p.s_1,
	p.s_2,
	p.s_500
FROM	cteSource AS s
PIVOT	(
		MAX(s.theValue)
		FOR s.theCol IN ([s_1], [s_2], [s_500])
	) AS p
WHERE	p.RecID = 1