InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 02:22:45 GMT20http://www.sqlservercentral.com/Forums/Topic1431108-392-1.aspxYes, Eugene's solution worked with compatibility_level=100 when I tested in my local machine.[code="sql"]select D.Item as [Category], COUNT(*) AS Countfrom dbo.TestCategory as Tcross apply (select * from dbo.fnSplit(T.[Category],'/') ) as Dgroup by D.Item [/code]As Eugene's earlier reply, I copied the DelimitedSplit8K() function from Jeff Moden's (your) article [url]http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url] and renamed as fnSplit() in my database. :-)[code="sql"]ALTER FUNCTION dbo.fnSplit--===== Define I/O parameters (@pString VARCHAR(8000), @pDelimiter CHAR(1))--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!RETURNS TABLE WITH SCHEMABINDING AS RETURN--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000... -- enough to cover VARCHAR(8000) WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front -- for both a performance gain and prevention of accidental "overruns" SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ),cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter) SELECT 1 UNION ALL SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter ),cteLen(N1,L1) AS(--==== Return start and length (for use in substring) SELECT s.N1, ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000) FROM cteStart s )--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1), Item = SUBSTRING(@pString, l.N1, l.L1) FROM cteLen l;[/code]Once again Thank you all for helping me :-)Fri, 15 Mar 2013 13:15:48 GMTromahhttp://www.sqlservercentral.com/Forums/Topic1431108-392-1.aspx[quote][b]romah (3/14/2013)[/b][hr]I found in other forums that "cross apply" only works if the compatibility_level is greater than 80.When I checked the compatibility_level of our database, it's 80.[code="sql"]SELECT compatibility_levelFROM sys.databases WHERE name = 'database_name'[/code]I think Eugene's solution works in greater compatibility-level but I don't have permission to change it.Thanks once again Eugene !:-)[/quote]NP. I am, however, concerned that Eugene is correct about the future. Please post your fnSplit function (ironically named) and lets see if we can make this code a bit more bullte proof for the future scalability..Thu, 14 Mar 2013 19:19:00 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1431108-392-1.aspxI found in other forums that "cross apply" only works if the compatibility_level is greater than 80.When I checked the compatibility_level of our database, it's 80.[code="sql"]SELECT compatibility_levelFROM sys.databases WHERE name = 'database_name'[/code]I think Eugene's solution works in greater compatibility-level but I don't have permission to change it.Thanks once again Eugene !:-)Thu, 14 Mar 2013 15:08:47 GMTromahhttp://www.sqlservercentral.com/Forums/Topic1431108-392-1.aspxI tried and I got another error for both @mytable and TestCategory Error:Msg 102, Level 15, State 1, Line 4Incorrect syntax near '.'.And I tried with select after cross apply. Again got the same error.[code="sql"]select D.Item as [Category], COUNT(*) AS Countfrom dbo.TestCategory as Tcross apply (select * from dbo.fnSplit(T.[Category],'/') ) as Dgroup by D.Item [/code]I test the fnSplit() function and it's returning correct valuesselect * from dbo.fnSplit('asp.net/c#/xml/html','/')Returns [b]Item Number[/b]---[b]Item[/b]1----------------asp.net2----------------c#3----------------xml4----------------htmlThu, 14 Mar 2013 14:28:38 GMTromahhttp://www.sqlservercentral.com/Forums/Topic1431108-392-1.aspxNote, dbo.DelimitedSplit8K is the fastest way to split sting in T-SQL (it only will marginally loose to CLR implementation). Also, what will happen if your Category will include 3 or more skills: HTML/ASP.NET/C#?You will need to amend Alan's solution in order to deal with this (making it slower). Using DelimitedSplit8K splitter, you don't need to worry about this and scalability.Try to make it working. I guess you have some other object in your database called Category. Try to put table alias at front of Category inside of function:[code="sql"]select d.Item as [Category], COUNT(*) AS Countfrom @mytable tcross apply dbo.fnSplit(t.[Category], '/') dgroup by d.Item [/code]Thu, 14 Mar 2013 13:21:22 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1431108-392-1.aspxAwesome Alan's code works perfect !!!I changed "#classes" to my table name "TestCategory" and it gave the result that I expected.Thank you Alan. and also thank you Eugene Elutin.How to accept that post as answer?Thu, 14 Mar 2013 12:24:45 GMTromahhttp://www.sqlservercentral.com/Forums/Topic1431108-392-1.aspxActually I copied your code and also a functiondeclare @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 Countfrom @mytable tcross apply dbo.fnSplit(Category, '/') dgroup 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 TestCategoryselect d.Item as Category, COUNT(*) AS Countfrom TestCategory tcross apply dbo.fnSplit(Category, '/') dgroup by d.ItemWhen I did like this, I got syntax error 'Category'My table is very simple as I already provided.[b]SNo[/b]---[b]Category[/b]1 ------- XML/HTML2 ------- ASP.NET3 ------- C#4 ------- ASP.NET/C#5 ------- C#/XML6 ------- HTML/ASP.NET7 ------- SQL8 ------- SQL/HTML9 ------- SQL/XML10 ------- XML11 ------- C#12 ------- ASP.NET13 ------- SQL14 ------- XML15 ------- SQLThu, 14 Mar 2013 12:17:53 GMTromahhttp://www.sqlservercentral.com/Forums/Topic1431108-392-1.aspx[quote][b]romah (3/14/2013)[/b][hr]Thank you SSCrazy for your quick reply.Now, I got the 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."[/quote]Can you post what you're running to get this error? Which Sql Server version are you using?What is Category in your case? Thu, 14 Mar 2013 11:53:21 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1431108-392-1.aspxWell, Eugene beat me to it and with something better than what I put together. [code="sql"]--Next time provide some DDL like so....--1) Lets setup that test dataIF OBJECT_ID('tempdb..#classes') IS NOT NULL DROP TABLE #classes;CREATE TABLE #classes (SNo int primary key, category varchar(20) NOT NULL);INSERT INTO #classesSELECT 1,'XML/HTML' UNION ALLSELECT 2,'ASP.NET' UNION ALLSELECT 3,'C#' UNION ALLSELECT 4,'ASP.NET/C#' UNION ALLSELECT 5,'C#/XML' UNION ALLSELECT 6,'HTML/ASP.NET' UNION ALLSELECT 7,'SQL' UNION ALLSELECT 8,'SQL/HTML' UNION ALLSELECT 9,'SQL/XML' UNION ALLSELECT 10,'XML' UNION ALLSELECT 11,'C#' UNION ALLSELECT 12,'ASP.NET' UNION ALLSELECT 13,'SQL' UNION ALLSELECT 14,'XML' UNION ALLSELECT 15,'SQL'-- The query:;WITH cat1 AS ( SELECT category FROM #classes WHERE CHARINDEX('/',category)=0),cat2 AS( SELECT category FROM #classes WHERE CHARINDEX('/',category)<>0),catall AS( SELECT category FROM cat1 UNION ALL SELECT SUBSTRING(Category,1,CHARINDEX('/',Category)-1) FROM cat2 UNION ALL SELECT SUBSTRING(Category,CHARINDEX('/',Category)+1,LEN(category)) FROM cat2)SELECT category, count(category) AS [count]FROM catallGROUP BY category-- be nice to your dbaDROP TABLE #classesGO[/code]Thu, 14 Mar 2013 11:52:06 GMTAlan.Bhttp://www.sqlservercentral.com/Forums/Topic1431108-392-1.aspxThank you SSCrazy for your quick reply.Now, I got the 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."Thu, 14 Mar 2013 11:49:04 GMTromahhttp://www.sqlservercentral.com/Forums/Topic1431108-392-1.aspxWith Jeff Moden help ( dbo.DelimitedSplit8K string split function - [url]http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url] )you can do just this: [code="sql"]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 Countfrom @mytable tcross apply dbo.DelimitedSplit8K (Category, '/') dgroup by d.Item[/code]Thu, 14 Mar 2013 11:27:38 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1431108-392-1.aspxI have a following table and I need to group by each topic and count them.[b]SNo [/b] [b] Category[/b]1 ------- XML/HTML2 ------- ASP.NET3 ------- C#4 ------- ASP.NET/C#5 ------- C#/XML6 ------- HTML/ASP.NET7 ------- SQL8 ------- SQL/HTML9 ------- SQL/XML10 ------- XML11 ------- C#12 ------- ASP.NET13 ------- SQL14 ------- XML15 ------- SQL[b]Expected Result:[/b][b]Category[/b] [b]Count[/b]C# -------- 4XML -------- 5HTML ------- 3ASP.NET --- 4SQL ------- 5Thu, 14 Mar 2013 10:39:09 GMTromah