mick burden (9/25/2012)
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/2012jim
frank
sue
Alan
Tom
George
Alice
Tony
Mike
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')
My apologies, I missed that post. :blush:
Here is one way to do that to get all your data across all the columns.
declare @SearchDate datetime = '20120924'
select item
from TestTable
cross apply dbo.DelimitedSplit8K(field2, '|') f2
where field1 = @SearchDate
union all
select item
from TestTable
cross apply dbo.DelimitedSplit8K(field3, '|') f2
where field1 = @SearchDate
union all
select item
from TestTable
cross apply dbo.DelimitedSplit8K(field4, '|') f2
where field1 = @SearchDate
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.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/