Loop thru a filed with ; separated values

Question

Post reply

Loop thru a filed with ; separated values

@DJ

SSCommitted

Points: 1989
More actions
November 12, 2010 at 3:17 am

#241086

Hi all.
I have a table that contains data about customer,seller, and visits.
The problem I have is that visits are ; seperated.
Example
customer,seller,visits
1000,Dan Jakobsen,04;06;08;10;12;14
This means that customer 1000 will have a visit from Dan Jakobsen in week 04,06,08,10.. and so on.
The combo cust+name is unique.
I have to create a procedure that generates a table looking like
1000,Dan Jakobsen,04
1000,Dan Jakobsen,06
1000,Dan Jakobsen,08
I have writen a code that iterates thru each line, but how do I get it to also check the visit and iterate thru that on each line, creating the above end result?
*****CODE START********
BEGIN
DECLARE
@j-2 as int,
@loop as int,
@sql nvarchar(4000),
@sql1 nvarchar(2000),
@visits nvarchar(255)
-- SET VALID IDENTIFIERS
set @loop = 10
set @j-2 = 1
-- SET LOOP QUANTITY
set @loop = (SELECT count(*) FROM catalog.dbo.[cust_visits tmp 1])
-- OUTER LOOP TERMINATE AT i = 371
WHILE @j-2 <= @loop
BEGIN
-- SET QUERY
set @sql2 = 'SELECT customer,seller,visits FROM catalog.dbo.[cust_visits tmp 1]'
set @sql = @sql1
EXEC (@sql)
-- COUNTERS
set @j-2 = @j-2+1
-- END OUTER LOOP
END
-- END BEGIN
END
*****CODE STOP********

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Here's a most probably much faster approach using the TallyTable based DelimitedSplit function.

DECLARE @tbl TABLE

(

customer INT, seller VARCHAR(50), visits VARCHAR(50)

)

INSERT INTO @tbl

SELECT 1000,'Dan Jakobsen','04;06;08;10;12;14' UNION ALL

SELECT 2000,'Else Somebody','01;11;18;100;12000;134324'

SELECT

t.customer,

t.seller,

s.Item

FROM @tbl t

CROSS APPLY

dbo.DelimitedSplit8K(visits,';') s

One of the later versions of the DelimitedSplit8K can be found here.

If you don't have a TallyTable yet, please see the related link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

WayneS SSC Guru Points: 95460 More actions · Answer 2

Whoops, Lutz beat me to it, and furthermore I didn't read everything correctly. So, I've removed what I did.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2