SQL Puzzle | Split & Count Puzzle

Puzzle Statement

The puzzle is simple. You have to split the data based on semi colon. This question was asked at an online forum. Well there are many methods to split data. I am here using simple XML method. You cannot use a loop or cursor. Please check input and expected output for details.

Sample Input

Expected output

Script
Use below script to create table and insert sample data in it.

--

CREATE TABLE SplitAndCount
(
	cols VARCHAR(100)
)
GO

INSERT INTO SplitAndCount (cols)
VALUES ('Ted;Joe;Mike'),('Ted;Joe'),('Ted')
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION #

--

SELECT SplittedString , COUNT(*) Counts FROM
(
    SELECT  CAST(CONCAT('<A>' , REPLACE(cols, ';' , '</A><A>' ) , '</A>' ) AS XML) Xmlcol FROM SplitAndCount
) s
CROSS apply
(
    SELECT ProjectData.D.value('.', 'VARCHAR(4)') as SplittedString
    FROM s.xmlcol.nodes('A') as ProjectData(D)
)a
GROUP BY a.SplittedString

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Kumar Khowal 

Http://MSBISkills.com