Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

read multiple fields with common delimited data into either a temp table or table variable without using cursors Expand / Collapse
Author
Message
Posted Tuesday, September 25, 2012 11:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 13,069, Visits: 11,908
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/2012
jim
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.

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1364190
Posted Tuesday, September 25, 2012 1:59 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:54 AM
Points: 287, Visits: 403
That'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?
Post #1364276
Posted Tuesday, September 25, 2012 2:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:25 PM
Points: 3,333, Visits: 7,193
If 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)

select item
from TestTable
cross apply dbo.DelimitedSplit8K(field2 + '|' +
field3 + '|' +
field4 + '|' +
field5 /*+ '|' + fieldN */, '|') f2




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1364281
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse