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:
Answer2(Best Ans):(by Hermann Cardenas)
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