0

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

edited may 30 13

created may 30 13 conradfrix