Q&A for cartographers, geographers and GIS professionals
select cast(t.value as int) PostID , count(t.value) c FROM PostHistory OUTER APPLY ( SELECT SUBSTRING(',' + substring(text,25, CHARINDEX (']',text,1) - 25) + ',', Number + 1, CHARINDEX(',', ',' + substring(text,25, CHARINDEX (']',text,1) - 25) + ',', Number + 1) - Number -1)AS VALUE FROM master..spt_values WHERE Type = 'P' AND Number <= LEN(',' + substring(text,25, CHARINDEX (']',text,1) - 25) + ',') - 1 AND SUBSTRING(',' + substring(text,25, CHARINDEX (']',text,1) - 25) + ',', Number, 1) = ',') as t where PostHistoryTypeID = 10 And Text like '{"OriginalQuestionIds":%' GROUP BY t.value ORDER BY count(t.value) desc