Solution to TSQL Beginners Challenge 18

;WITH HtmlTable(Id, Tag)
AS 
(
	SELECT t.Id, substring(t.HtmlText, tally.N+1, charindex('>',t.HtmlText, tally.N)-tally.N-1)
	FROM @t t, ufn_Tally2(1, 8000,1) tally 
	WHERE substring(t.HtmlText, Tally.N, 1) = '<'
	AND substring(t.HtmlText, Tally.N+1, 1) <> '/'

)
,ResTable2(Id, Tag, TagCount)
AS
(
	SELECT Id, Tag, COUNT(*) FROM HtmlTable GROUP BY Id, Tag
)
,ResTable(Id, TagString)
AS
(
	SELECT DISTINCT Id, (SELECT ', ' + HT.Tag+'(Found: '+CAST(HT.TagCount AS VARCHAR(10))+' time'+(CASE WHEN HT.TagCount=1 THEN ')' ELSE 's)' END)
	FROM ResTable2 HT WHERE HT.Id = _HT.Id FOR XML PATH(''))X FROM ResTable2 _HT 
)
SELECT ID, STUFF(TagString, 1, 2, '') [TagNamesOccurance] FROM ResTable
ORDER BY Id

Tags: