looping through field in table

Dear All

I have a table, and in one of the fields is the following information:
1,2,3,4,5, etc...

How do I 'loop' through this field to remove each comma and put the number into it's own field in another table - so remove the first comma, and put the number 1 into its own field, remove the 2nd comma and put the number 2 into it's own field etc, until all the numbers are in their own fields.

So it ends up being like this:

Col1 Col2 Col3 Col4 Col5 Col n.............
1 2 3 4 5 n...........

There may not be 5 numbers in the field, sometimes more, sometimes less, so i need to be able to tell when there are no more commas and numbers left

Your help is much appreciated

Thanks

GillI have a function for extracting the nth element of a delimited string, if you can use functions try this:

-- =============================================
-- Create inline function
-- =============================================
IF objectproperty(object_id(N'GetStringElement'),'IsS calarFunction') = 1
DROP FUNCTION GetStringElement
GO

CREATE FUNCTION GetStringElement(
@.String varchar(100)
, @.Element int
, @.Seperator char(1) = ',')

RETURNS varchar(100)
AS
begin
declare @.Pass int, @.Index int, @.LastIndex int, @.Return varchar(100)

if (@.String like '%' + replicate(',%',@.Element - 1)) begin
select @.Pass = 1
, @.Index = 1
, @.LastIndex = 0

while (@.Pass <= @.Element) begin
select @.LastIndex = case @.Index when 1 then 0 else @.Index end
, @.Index = charindex(',',@.String,@.Index + 1)
, @.Pass = @.Pass + 1
end

if (@.LastIndex > 0 and @.Index = 0) set @.Index = len(@.String) + 1

set @.Return = substring(@.String,@.LastIndex + 1, @.Index - @.LastIndex - 1)
end

RETURN @.Return
end
GO

if object_id('tempdb..#psy') is not null
drop table #psy

create table #psy(f1 int identity(1,1) not null,f2 varchar(25),col1 varchar(10),col2 varchar(10),col3 varchar(10),col4 varchar(10),col5 varchar(10),col6 varchar(10))

insert into #psy (f2) values('1,2,3,4,5')
insert into #psy (f2) values('a,b,c')
insert into #psy (f2) values('George,John,Paul,Ringo')

select * From #psy

declare @.pass int, @.ColCount int, @.TSQL varchar(255)
select @.pass = 1
, @.ColCount = 6
while (@.pass <= @.ColCount) begin
select @.TSQL = 'update #psy ' +
'set col' + cast(@.pass as varchar) + ' = dbo.GetStringElement(f2,' + cast(@.pass as varchar) + ',default)'
, @.pass = @.pass + 1
exec(@.TSQL)
end

select * from #psy

IF objectproperty(object_id(N'GetStringElement'),'IsS calarFunction') = 1
DROP FUNCTION GetStringElement

if object_id('tempdb..#psy') is not null
drop table #psy|||if functions are not your thing you could try:
if object_id('tempdb..#psy') is not null
drop table #psy

create table #psy(f1 int identity(1,1) not null,f2 varchar(25),col1 varchar(10),col2 varchar(10),col3 varchar(10),col4 varchar(10),col5 varchar(10),col6 varchar(10))

insert into #psy (f2) values('1,2,3,4,5')
insert into #psy (f2) values('a,b,c')
insert into #psy (f2) values('George,John,Paul,Ringo')

select * From #psy

declare @.RecordID int, @.TSQL varchar(255), @.pass int
, @.Index int, @.LastIndex int, @.Return varchar(100)
, @.String varchar(100), @.Element int

select @.RecordID = min(f1) from #psy
while (@.RecordID is not null) begin
select @.String = f2 from #psy where f1 = @.RecordID

select @.Index = 1
, @.LastIndex = 0
, @.pass = 1

while (@.Index > 0) begin
select @.LastIndex = case @.Index when 1 then 0 else @.Index end
, @.Index = charindex(',',@.String,@.Index + 1)

if (@.LastIndex > 0 and @.Index = 0)
set @.Return = substring(@.String,@.LastIndex + 1, 100)
else
set @.Return = substring(@.String,@.LastIndex + 1, @.Index - @.LastIndex - 1)

select @.TSQL = 'update #psy ' +
'set col' + cast(@.pass as varchar) + ' = ''' + @.Return + ''' ' +
'where f1 = ' + cast(@.RecordID as varchar)
, @.pass = @.pass + 1

exec(@.TSQL)
end

select @.RecordID = min(f1) from #psy where f1 > @.RecordId

end

select * from #psy

Looking for 'upsizing' from Access/PivotCharts

Hi All!

I'm doing analysis of system performance metrics recorded from a web application performance test. I have approximately 15 tables, each with unique system and test metrics, ranging in size from 6 thousand to 6 million rows. E.g:

iostats: time, host, disk, read_bps, write_bps, ...
netstats: time, host, active_conns, xmit_errs, resets, ...
teststats: time, test_name, virtual_users, ...
timerstats: time, script_name, timer_name, elapsed_time, ...

Last year i used MS Access + pivot tables to do the analysis of this information, but Access isn't cutting it this year. It frequently goes out to lunch and never returns whenever I try to build the charts i'm looking for (most of which use PivotChart views)

I've spent a few days trying to get my head around using SSAS for this purpose, but have run into a few stumbling blocks

1 - The information in the 15 tables is loosely correlated on time of day, some have one sample per second, others one sample every thirty seconds, and still others are whenever an update decides to roll in. I'm having a very difficult time figuring out how to make a usable 'time' dimension that i can use to chart the information.

2 - I seem to constantly run into 'missing attribute value' errors when trying to build and deploy my analysis services projects that contain cubes. Frequently this seems to be due to cases where i don't have specific information on a specific dimension (e.g. host / cpu / etc) for a given time period.

3 - I'm not sure how to visualize some of the output from the cube interactions that do work. I can get a table working in the cube browser, but how do i chart that? Is the Excel AS plugin the only approach available? I tried to view the tables via access linked tables, but a) Access (2002) complains it can't modify tables against SQL 2005, and b) Access doesn't pull the entire set of data back, just the first 50k records or so.

4 - If i want to calculate disk utilization on a per disk per host basis, would that be a valid use of a dimension hierarchy?

I'm kind of running out of time. My game plan for today is to recalculate all tables to exactly one minute sample intervals and try again, hoping that the reduced row count will make things a little more smooth.

Thanks for any advice!!!

Bob

Several ideas for you:

Try to see if Office 2007 beta is going to work for you. Install Office2007 beta and try if you can fit your data sizes there.

Second. If you are using Analysis Services Excel add-in is not the only choice. You can use Excel Pivot Tables to connect to Analysis Services. You can use many other client applications to diplay Analysis Services data.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Hi Edward!

Thanks for the response. I may take a look at office 2007 today to see how it goes.

Looking for file s80305i.exe

Hi all
I'm trying to track down a copy of the file s80305i.exe for use in a SQL ser
ver 2000 upgrade and was wondering if anyone knew of a location as it is a b
roken link on the Microsoft website.
My email is ian.hayward@.preferredmortgages.com if anyone could email me a co
py.
Many thanks in advance.
IanPlease contact PSS and the engineer who you work with should be able to
locate the hotfix for you internally.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I believe that you got the link from
http://support.microsoft.com/?kbid=300676, correct? If you cut & paste the
following text into the address bar of your browser,
ftp://ftp.microsoft.com/bussys/sql/...r/sql80/setup/, does it work?
I ask because even though clicking on the link worked for me a couple of
minutes ago, earlier this week it wasn't. When I manually put that link into
the address bar of my copy of Internet Explorer earlier this week, it did.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"ihayward" <ihayward.1a7j53@.mail.webservertalk.com> wrote in message
news:ihayward.1a7j53@.mail.webservertalk.com...
> Hi all
> I'm trying to track down a copy of the file s80305i.exe for use in a
> SQL server 2000 upgrade and was wondering if anyone knew of a location
> as it is a broken link on the Microsoft website.
> My email is ian.hayward@.preferredmortgages.com if anyone could email me
> a copy.
> Many thanks in advance.
> Ian
>
> --
> ihayward
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message330249.html
>|||That link is definitely cursed :-)
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:ORSj7jldEHA.212@.TK2MSFTNGP12.phx.gbl...
> I believe that you got the link from
> http://support.microsoft.com/?kbid=300676, correct? If you cut & paste the
> following text into the address bar of your browser,
> ftp://ftp.microsoft.com/bussys/sql/...r/sql80/setup/, does it work?
> I ask because even though clicking on the link worked for me a couple of
> minutes ago, earlier this week it wasn't. When I manually put that link
into
> the address bar of my copy of Internet Explorer earlier this week, it did.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "ihayward" <ihayward.1a7j53@.mail.webservertalk.com> wrote in message
> news:ihayward.1a7j53@.mail.webservertalk.com...
>|||can you send me this file too...
thank you
gallus
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||can I get this file too...
my email adress is gallus68@.web.de
thank you
gallus
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Looking for existing tool/product to integrate in MS CMS/SQL based website

Hi all!
I am the IT manager for the Flemish Red Cross and I am looking for an
existing tool/product that we can use to implement a full text-search
(including webpages, Word documents and PDF's) on our new website, thta
we're building on MS CMS and SQLServer.
Any suggestions? We don't really have the time to develop something
ourselves, and I am convinced that there are existing tools or products that
can do the job.
We're willing to spend some money on it, but not in the area of 10000
USD/EUR per processor...
Any help would be greatly appreciated.
I can be e-mailed directly at werner.huysmans@.advalvas.be or
werner.huysmans@.rodekruis.be
Thx in advance!
Werner
SQL Full Text Search is optimal for this and it is free.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Werner huysmans" <werner.huysmans@.advalvas.be> wrote in message
news:pHrhf.61734$rA.54376@.blueberry.telenet-ops.be...
> Hi all!
> I am the IT manager for the Flemish Red Cross and I am looking for an
> existing tool/product that we can use to implement a full text-search
> (including webpages, Word documents and PDF's) on our new website, thta
> we're building on MS CMS and SQLServer.
> Any suggestions? We don't really have the time to develop something
> ourselves, and I am convinced that there are existing tools or products
> that
> can do the job.
> We're willing to spend some money on it, but not in the area of 10000
> USD/EUR per processor...
> Any help would be greatly appreciated.
> I can be e-mailed directly at werner.huysmans@.advalvas.be or
> werner.huysmans@.rodekruis.be
> Thx in advance!
> Werner
>