Sqlauthority.com Quiz\Puzzle Answer (Splitting Strings Puzzle)

Puzzle Questions : Read here http://blog.sqlauthority.com/2016/06/02/sql-server-puzzle-splitting-strings-puzzle/#comment-1967409

Answers1:
SELECT SplitMyString,Count(*) Counts FROM ( SELECT
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS SplitMyString
FROM
(SELECT CAST('<XMLRoot><R>' + REPLACE(cols,';','</R><R>') + '</R></XMLRoot>' AS XML) AS x FROM   SplitWithCount

)t CROSS APPLY x.nodes('/XMLRoot/R')m(n) ) M GROUP BY SplitMyString

Output:

Answer2(Best Ans):(by Hermann Cardenas)


declare @sql varchar(max) = ''
select @sql = @sql + 'as nm union all select ''' + replace(cols, ';', ''' as nm union all select ''') + '''' from splitwithcount
set @sql = 'select nm, count(*) cnt from (' + SUBSTRING(@sql, 17, len(@sql)) + ') a group by nm'

exec(@sql)

Comments

Archive

Contact Form

Send