Solution to TSQL Beginners Challenge 18
DECLARE @t TABLE(ID INT IDENTITY, HtmlText VARCHAR(Max))
INSERT INTO @t(HtmlText)
SELECT 'This is challenge #18';
WITH t1 AS (
SELECT ID, CAST((Len(HtmlText)- LEN(Replace(HtmlText,'', '')))/6 AS varchar(1)) as count, 'Body' as text
from @t
UNION
SELECT ID, CAST((Len(HtmlText)- LEN(Replace(HtmlText,'', '')))/6 AS varchar(1)) as count, 'Font' as text
from @t
UNION
SELECT ID, CAST((Len(HtmlText)- LEN(Replace(HtmlText,'', '')))/6 AS varchar(1)) As count, 'Html' as text
FROM @t), t2 AS (
SELECT ID, CASE WHEN count = '1' THEN text + '(Found: '+count+' time)'
ELSE text + '(Found: '+count+' times)' END AS Tag
FROM t1)
SELECT DISTINCT b.ID,
(STUFF (( SELECT DISTINCT ', ' + Tag from t2 a
WHERE a.ID=b.ID
FOR XML PATH ('')),1,1,'')) as TagNamesOccurance
FROM t1 b
Tags: