Actually I copied your code and also a function
declare @mytable table (SNo int, Category varchar(100))
insert @mytable
select 1, 'XML/HTML'
union select 2, 'ASP.NET'
union select 3, 'C#'
union select 4, 'ASP.NET/C#'
union select 5, 'C#/XML'
union select 6, 'HTML/ASP.NET'
union select 7, 'SQL'
union select 8, 'SQL/HTML'
union select 9, 'SQL/XML'
union select 10, 'XML'
union select 11, 'C#'
union select 12, 'ASP.NET'
union select 13, 'SQL'
union select 14, 'XML'
union select 15, 'SQL'
select d.Item as Category, COUNT(*) AS Count
from @mytable t
cross apply dbo.fnSplit(Category, '/') d
group by d.Item
When I tried with your code, I got following error:
"Category" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.
Also tried to use my table TestCategory
select d.Item as Category, COUNT(*) AS Count
from TestCategory t
cross apply dbo.fnSplit(Category, '/') d
group by d.Item
When I did like this, I got syntax error 'Category'
My table is very simple as I already provided.
SNo---Category
1 ------- XML/HTML
2 ------- ASP.NET
3 ------- C#
4 ------- ASP.NET/C#
5 ------- C#/XML
6 ------- HTML/ASP.NET
7 ------- SQL
8 ------- SQL/HTML
9 ------- SQL/XML
10 ------- XML
11 ------- C#
12 ------- ASP.NET
13 ------- SQL
14 ------- XML
15 ------- SQL