InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 14:47:44 GMT20http://www.sqlservercentral.com/Forums/Topic1332892-169-1.aspxWe can also use comma separted list to a table instead of DelimitedSplit8K function.Both function would produce same resultsCREATE FUNCTION [dbo].[SplitList] ( @List nvarchar(max), @SplitOn nvarchar(5) ) RETURNS @RtnValue table ( Id int identity(1,1), Value nvarchar(100) ) AS BEGIN While (Charindex(@SplitOn,@List)>0) Begin Insert Into @RtnValue (value) Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List)) End Insert Into @RtnValue (Value) Select Value = ltrim(rtrim(@List)) Return ENDFri, 21 Sep 2012 00:32:07 GMTsaltpepohttp://www.sqlservercentral.com/Forums/Topic1332892-169-1.aspx[quote][b]swathi nareddy (9/6/2012)[/b][hr]Can I use below function instead of DelimitedSplit8K() function CREATE FUNCTION SPLIT( @RowData nvarchar(2000), @SplitOn nvarchar(5)) RETURNS @RtnValue table ( Id int identity(1,1), Data nvarchar(100)) AS BEGIN Declare @Cnt int Set @Cnt = 1 While (Charindex(@SplitOn,@RowData)>0) Begin Insert Into @RtnValue (data) Select Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1))) Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData)) Set @Cnt = @Cnt + 1 End Insert Into @RtnValue (data) Select Data = ltrim(rtrim(@RowData)) ReturnENDand required query is select * from tblCategories LEFT JOIN ( SELECT * FROM tblEmployees cross apply dbo.SPLIT(CategoryIDs,',') ) A On tblCategories.CategoryID = A.DATA[/quote]Yes, but only if poor performance is a requirement of your application.Thu, 06 Sep 2012 23:38:36 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1332892-169-1.aspxCan I use below function instead of DelimitedSplit8K() function CREATE FUNCTION SPLIT( @RowData nvarchar(2000), @SplitOn nvarchar(5)) RETURNS @RtnValue table ( Id int identity(1,1), Data nvarchar(100)) AS BEGIN Declare @Cnt int Set @Cnt = 1 While (Charindex(@SplitOn,@RowData)>0) Begin Insert Into @RtnValue (data) Select Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1))) Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData)) Set @Cnt = @Cnt + 1 End Insert Into @RtnValue (data) Select Data = ltrim(rtrim(@RowData)) ReturnENDand required query is select * from tblCategories LEFT JOIN ( SELECT * FROM tblEmployees cross apply dbo.SPLIT(CategoryIDs,',') ) A On tblCategories.CategoryID = A.DATAThu, 06 Sep 2012 06:14:17 GMTSWATHhttp://www.sqlservercentral.com/Forums/Topic1332892-169-1.aspxHi Lowell thank youI am quite new to SQL SERVER.I didn't understand your solution for this. my question is where can i find delimitedsplit8k()function?and how can i use it? could you please explain it Thanks& RegardsSwathiThu, 06 Sep 2012 01:00:25 GMTSWATHhttp://www.sqlservercentral.com/Forums/Topic1332892-169-1.aspxswathi that's a great way to show an alternate solution.The only thing that bothers me is that solution only works if there are only 9 categories, 1-9, since you are assuming a single digit category Id;As soon as he has more categories than that, he'll need to rewrite it.Wed, 05 Sep 2012 05:16:49 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1332892-169-1.aspxwe can do without using master.dbo.DelimitedSplit8K() SELECT A.employeeid,A.employeename,B.categoryname,C.categoryname FROM ( SELECT employeeid,employeename, SUBSTRING(categoryids,1,CHARINDEX(',',categoryids)-1) categoryid1, SUBSTRING(categoryids,CHARINDEX(',',categoryids)+1,LEN(categoryids)) categoryid2 FROM tblemployees )A INNER JOIN tblcategories B ON A.categoryid1=B.categoryid INNER JOIN tblcategories C ON A.categoryid2=C.categoryid i hope this will be useful to someone:-)Wed, 05 Sep 2012 04:43:03 GMTSWATHhttp://www.sqlservercentral.com/Forums/Topic1332892-169-1.aspxThanks for replying Boss for my question....Its working fine thanks a lot.Mon, 23 Jul 2012 00:49:33 GMTvenkidesaikhttp://www.sqlservercentral.com/Forums/Topic1332892-169-1.aspxnice first post, you provided quite a bit of info.my first point, is in the future, if you can provide your data as CREATE TABLE / INSERT INTO statements like this, anyone can easily see your data and provide test scripts for you:[code]drop table tblEmployeesdrop table tblCategoriesCREATE TABLE tblCategories(CategoryID[INT],CategoryName[VARCHAR](30))CREATE TABLE tblEmployees(EmployeeID[INT],EmployeeName[VARCHAR](30),CategoryIDs[VARCHAR](30))INSERT INTO tblCategories( CategoryID,CategoryName)SELECT '1','AAA' UNION ALLSELECT '2','BBB' UNION ALLSELECT '3','CCC'INSERT INTO tblEmployees(EmployeeID,EmployeeName,CategoryIDs) SELECT '1','XXX','1,3' UNION ALL SELECT '2','YYY','2,3' UNION ALL SELECT '3','ZZZ','1,2'[/code]Next, you want to avoid storing multiple values in a single column, otherwise you run into problems like you are having now.instead of having '1,2' as a varchar field in a row, you should have two rows in the table, both with foreign keys to the category instead.a work around for that is to use the DelimitedSpilt8K splitter function here on SSC:[url]http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]with that function, you can split that comma-delimited list into seperate rows (like they should have been)[code] SELECT * FROM tblEmployees cross apply master.dbo.DelimitedSplit8K(CategoryIDs,',')[/code]then using those results as a sub query, you could join them on category:[code] select * from tblCategories LEFT OUTER JOIN ( SELECT * FROM tblEmployees cross apply master.dbo.DelimitedSplit8K(CategoryIDs,',') ) MyAlias On tblCategories.CategoryID = MyAlias.Item[/code]hope that helps get you started.Fri, 20 Jul 2012 06:11:55 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1332892-169-1.aspxHi All,I have two tables for example,tblCategories(CategoryID[INT],CategoryName[VARCHAR])tblEmployees(EmployeeID[INT],EmployeeName[VARCHAR],CategoryIDs[VARCHAR])Example OUTPUT Like,tblCategories:CategoryID CategoryName1 AAA2 BBB3 CCCtblEmployees:EmployeeID EmployeeName CategoryIDs1 XXX 1,32 YYY 2,33 ZZZ 1,2I want to join both the tables & i want to get the resultis it possible?if it is possible how to join the tablePlease give me the query& also result for me.Thanks In Advance,Venkatesh.Fri, 20 Jul 2012 05:46:18 GMTvenkidesaik