【Question】
CREATE TABLE dbo.Employees

(

Class VarChar(40) NOT NULL,

Teacher VarChar(200) NOT NULL,

);

Insert Into dbo.Employees(Class, Teacher) Values(‘001’,‘Jackie, Ethan, Kay’);

Insert Into dbo.Employees(Class, Teacher) Values(‘002’,‘Ethan’);

Insert Into dbo.Employees(Class, Teacher) Values(‘003’,‘Ethan, Kay’);

Insert Into dbo.Employees(Class, Teacher) Values(‘004’,‘Jackie, Kay’);

Insert Into dbo.Employees(Class, Teacher) Values(‘005’,‘Jackie’);

Insert Into dbo.Employees(Class, Teacher) Values(‘006’,‘Jackie, Ethan, Kay’);

Insert Into dbo.Employees(Class, Teacher) Values(‘007’,‘Ethan, Kay’);

Insert Into dbo.Employees(Class, Teacher) Values(‘008’,‘Jackie, Ethan, Kay’);

Insert Into dbo.Employees(Class, Teacher) Values(‘009’,‘Jackie, Kay’);

The expected result:
Teacher  Count
Jackie     6
Ethan     6
Kay      7

【Answer】
The natural way of doing this is splitting Teacher into multiple rows, and then union and summarize them. But it’s complicated to do it in SQL. Here I handle it in SPL, which generates intuitive an easy-to-understand code:

A2 splits each Teacher into a sequence and unions all Teacher values; then groups and counts each Teacher name.

esProc offers JDBC interface to make it function like a database. See How to Call an SPL Script in Java to learn more.