InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 20:19:46 GMT20http://www.sqlservercentral.com/Forums/Topic1363472-1292-1.aspxIf you want something dynamic, you will need dynamic sql code.Another way to do it would be concatenating the fields (up to 19 with your current length)[code="sql"]select itemfrom TestTablecross apply dbo.DelimitedSplit8K(field2 + '|' + field3 + '|' + field4 + '|' + field5 /*+ '|' + fieldN */, '|') f2[/code]Tue, 25 Sep 2012 14:16:53 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1363472-1292-1.aspxThat's fine Sean, I've done the same myself. As for my problem, although your solution looks good and as it stands would work with my table, I've put the amount of fields as 4 but in reality it stands at 12 and could grow in the future. Is there some kind of dynamic way I can search across the fields?Tue, 25 Sep 2012 13:59:55 GMTmick burdenhttp://www.sqlservercentral.com/Forums/Topic1363472-1292-1.aspx[quote][b]mick burden (9/25/2012)[/b][hr]is the following that I put in a thread earlier not good enough? it created a table with the desired data inputted. What I wanted is an output that gave me the following. I wanted the data for 24/Sep/2012jimfranksueAlanTomGeorgeAliceTonyMike[code]GO-- Creating Test TableCREATE TABLE testtable(field1 datetime, field2 VARCHAR(400), field3 VARCHAR(400),field4 VARCHAR(400))GO-- Inserting Data into TableINSERT INTO testtable(field1,field2,field3,field4)VALUES('24/Sep/2012','jim|frank|sue','Alan|Tom|George','Alice|Tony|Mike')INSERT INTO testtable(field1,field2,field3,field4)VALUES('25/Sep/2012','Tony|Steph|Al','Alice|Tony|Mike','Reg|Steve|John')[/code][/quote]My apologies, I missed that post. :blush:Here is one way to do that to get all your data across all the columns.[code]declare @SearchDate datetime = '20120924'select item from TestTable cross apply dbo.DelimitedSplit8K(field2, '|') f2 where field1 = @SearchDateunion allselect item from TestTable cross apply dbo.DelimitedSplit8K(field3, '|') f2 where field1 = @SearchDateunion allselect item from TestTable cross apply dbo.DelimitedSplit8K(field4, '|') f2 where field1 = @SearchDate[/code]You can find the most current version of the DelimitedSplit8K function in the article about splitting strings in my signature. Make sure you read the article and understand what this function is doing. Hope this helps.Tue, 25 Sep 2012 11:20:56 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1363472-1292-1.aspxis the following that I put in a thread earlier not good enough? it created a table with the desired data inputted. What I wanted is an output that gave me the following. I wanted the data for 24/Sep/2012jimfranksueAlanTomGeorgeAliceTonyMike[code]GO-- Creating Test TableCREATE TABLE testtable(field1 datetime, field2 VARCHAR(400), field3 VARCHAR(400),field4 VARCHAR(400))GO-- Inserting Data into TableINSERT INTO testtable(field1,field2,field3,field4)VALUES('24/Sep/2012','jim|frank|sue','Alan|Tom|George','Alice|Tony|Mike')INSERT INTO testtable(field1,field2,field3,field4)VALUES('25/Sep/2012','Tony|Steph|Al','Alice|Tony|Mike','Reg|Steve|John')[/code]Tue, 25 Sep 2012 08:24:12 GMTmick burdenhttp://www.sqlservercentral.com/Forums/Topic1363472-1292-1.aspx[quote][b]mick burden (9/25/2012)[/b][hr]That works well, thank you, but I need it to go across all the fields at once, adding all the data to a single table or recordset[/quote]So then for the second time in this thread, if you need specific coding help you need to post ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. The first link in my signature will help you put that all together. Without this we are just shooting in the dark. Help us help you and you will be rewarded with tested, accurate and fast code.Tue, 25 Sep 2012 07:49:24 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1363472-1292-1.aspx[quote][b]saltpepo (9/25/2012)[/b][hr]Using function u can get desired result without cursors.I created split function above use that.Declare @asd nvarchar(100) select @asd=Field1 from Test where create_dt='24/Sep/2012' Select Data as Field1 from Split(@asd,'|')[/quote]That function will work but it is not as good for performance. You are using a while loop which is pretty much the same thing as a cursor. Take a look at the solution at the link in my signature about splitting strings. It will blow the doors off the while loop approach for performance.Tue, 25 Sep 2012 07:47:24 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1363472-1292-1.aspxThat works well, thank you, but I need it to go across all the fields at once, adding all the data to a single table or recordsetTue, 25 Sep 2012 07:41:39 GMTmick burdenhttp://www.sqlservercentral.com/Forums/Topic1363472-1292-1.aspxUsing function u can get desired result without cursors.I created split function above use that.Declare @asd nvarchar(100) select @asd=Field1 from Test where create_dt='24/Sep/2012' Select Data as Field1 from Split(@asd,'|')Tue, 25 Sep 2012 03:54:12 GMTsaltpepohttp://www.sqlservercentral.com/Forums/Topic1363472-1292-1.aspxMy appologies, I've now created a script to make a table containing the data... I hope this helpsUSE test GO -- Creating Test Table CREATE TABLE testtable(field1 datetime, field2 VARCHAR(400), field3 VARCHAR(400),field4 VARCHAR(400)) GO -- Inserting Data into Table INSERT INTO testtable(field1,field2,field3,field4) VALUES('24/Sep/2012','jim|frank|sue','Alan|Tom|George','Alice|Tony|Mike') INSERT INTO testtable(field1,field2,field3,field4) VALUES('25/Sep/2012','Tony|Steph|Al','Alice|Tony|Mike','Reg|Steve|John')Tue, 25 Sep 2012 03:39:15 GMTmick burdenhttp://www.sqlservercentral.com/Forums/Topic1363472-1292-1.aspx[quote][b]mick burden (9/24/2012)[/b][hr]if I have a table with fields which contain common delimited data in them, is there a way I can read them into some kind of temporary table, my example might help me explain what I'm after[code=plain]field1 .............. field2................. field3........................... field424/Sep/2012.......jim|frank|sue.......Alan|Tom|George...........Alice|Tony|Mike 25/Sep/2012.......Tony|Steph|Al.....etc[/code]I'd like to be able to query this table by date, so if I put in select where field1 = '24/Sep/2012' it would produce the data in one field likejimfranksueAlanTomGeorge... etcwithout using cursors, is this possible?Sorry for the bad formatting, not sure how to type it in here[/quote]As Sean points out, the data is a bit vague. Is it something like this?[code="sql"]SELECT field1, field2, field3, field4FROM ( SELECT field1 = CAST('24/Sep/2012' AS DATETIME), field2 = 'jim|frank|sue', field3 = 'Alan|Tom|George', field4 = 'Alice|Tony|Mike' UNION ALL SELECT '25/Sep/2012', 'Tony|Steph|Al', NULL, NULL) d[/code]Tue, 25 Sep 2012 03:21:41 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1363472-1292-1.aspxMight be u get hel with dis.CREATE TABLE dbo.Test ( id int, create_dt DATETIME NOT NULL, Field1 NVARCHAR(100) NOT NULL, Field2 NVARCHAR(2500) NOT NULL );GOInsert TestSelect '1','24/Sep/2012','.jim|frank|sue','Alan|Tom|George' union allSelect '2','25/Sep/2012','jim|frank|sue','jim|frank|sue' Insert TestSelect '1','24/Sep/2012','.jim|frank|sue','Alan|Tom|George' union allSelect '2','25/Sep/2012','jim|frank|sue','jim|frank|sue' GODeclare @asd nvarchar(100) select @asd=Field1 from Test where create_dt='24/Sep/2012' Select Data as Field1 from Split(@asd,'|') CreateFUNCTION [dbo].[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)) ReturnENDTue, 25 Sep 2012 02:59:27 GMTsaltpepohttp://www.sqlservercentral.com/Forums/Topic1363472-1292-1.aspx[quote][b]mick burden (9/24/2012)[/b][hr]if I have a table with fields which contain common delimited data in them, is there a way I can read them into some kind of temporary table, my example might help me explain what I'm after[code=plain]field1 .............. field2................. field3........................... field424/Sep/2012.......jim|frank|sue.......Alan|Tom|George...........Alice|Tony|Mike 25/Sep/2012.......Tony|Steph|Al.....etc[/code]I'd like to be able to query this table by date, so if I put in select where field1 = '24/Sep/2012' it would produce the data in one field likejimfranksueAlanTomGeorge... etcwithout using cursors, is this possible?Sorry for the bad formatting, not sure how to type it in here[/quote]Can't provide much help with the coding because there is nothing to work with here. If you need help with the actual code you need to look at the first link in my signature about best practices when posting.To parse your string you should take a look at the article in my signature about splitting strings. The logic found there is what you are ultimately going to need to solve this.Mon, 24 Sep 2012 08:37:43 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1363472-1292-1.aspxif I have a table with fields which contain common delimited data in them, is there a way I can read them into some kind of temporary table, my example might help me explain what I'm after[code=plain]field1 .............. field2................. field3........................... field424/Sep/2012.......jim|frank|sue.......Alan|Tom|George...........Alice|Tony|Mike 25/Sep/2012.......Tony|Steph|Al.....etc[/code]I'd like to be able to query this table by date, so if I put in select where field1 = '24/Sep/2012' it would produce the data in one field likejimfranksueAlanTomGeorge... etcwithout using cursors, is this possible?Sorry for the bad formatting, not sure how to type it in hereMon, 24 Sep 2012 07:15:00 GMTmick burden