August 8, 2013 at 9:47 pm
Hi all,
This is my first post here so hopefully I'll include everything that's needed. Please also be aware that I am self taught so I may not do things as easy as possible. I'm using SQL Server 2005.
I have some data that contains a string with comma separated values in it and I have used a cursor and a loop to load this data and separate it into another table. My problem is that I need this data available in a View so that it is always up to date with the latest values. I can't use a trigger on the table as the database is shared so I can create new tables, views, stored procs etc but I can't alter existing items.
Here is some sample test data:
create table TempTesting
(ID int,
TestData varchar(80))
insert into TempTesting
values (1122, '11111,222228,33333,44444')
insert into TempTesting
values (12345, '12345,54321')
insert into TempTesting
values (876543, '25847,369587,98758,145289,425986,76532')
insert into TempTesting
values (255, '951458')
And a table for the results:
create table TempResults
(ID int,
TestResult int)
And this is the code I use to separate the data:
declare @string varchar(100)
declare @result varchar(10)
declare @stringlocation int
declare @ID int
declare cursor_name Cursor
for
select ID, TestData from TempTesting
open cursor_name
Fetch next from Cursor_name into @ID, @string
WHILE (@@FETCH_STATUS <> -1)
Begin
select @stringlocation = PATINDEX('%,%',@string)
while @stringlocation > 0
begin
select @result = left(@string,@stringlocation-1), @string = right(@string,(len(@string)-@stringlocation))
insert into TempResults (ID, TestResult)
select @ID, convert(int,@result)
where not exists (select 1 from TempResults where ID = @ID and TestResult = @result)
select @stringlocation = PATINDEX('%,%',@string)
continue
end
insert into TempResults (ID, TestResult)
select @ID, convert(int,@string)
where not exists (select 1 from TempResults where ID = @ID and TestResult = @string)
and @string is not null
Fetch next from Cursor_name into @ID, @string
End
Close cursor_name
Deallocate cursor_name
--delete from tempresults
So what I need is to be able to create a View which looks like the results table as the data will be changing constantly. Is this possible?
August 8, 2013 at 10:03 pm
Oops sorry. I just noticed I put this into SQL Server 2008 instead of 2005. Can someone move it please?
August 9, 2013 at 12:55 am
A cursor is horrible horrible thing. You usually try to avoid this in SQL Server and go for a set-based solution.
This article describes a very performant function to split your strings. It's not an easy article, so take your time reading it.
Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]
This function will knock the socks off the cursor and it will give you an easy way to use it in your queries.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 11, 2013 at 5:18 pm
Thanks for the reply. I had a look at that page and it's going to take me some time to understand that so I'll see how I go.
August 11, 2013 at 6:54 pm
Ok so I can get the 'CSV Splitter' working but that only does a string. I need to read the data in from a table which could have any number of rows. I used a cursor in mine to read in each row in the table and only used a loop for the actual splitter.
I also need this to be in a View as it could be updated by any number of people at any time.
Is there any other help out there as to how I can do this in a View please?
August 12, 2013 at 2:56 am
jamie 82947 (8/11/2013)
Ok so I can get the 'CSV Splitter' working but that only does a string. I need to read the data in from a table which could have any number of rows. I used a cursor in mine to read in each row in the table and only used a loop for the actual splitter.I also need this to be in a View as it could be updated by any number of people at any time.
Is there any other help out there as to how I can do this in a View please?
Not an expert in this syntax, but I believe you can call CROSS APPLY on the split function for every row of your table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 12, 2013 at 7:45 am
Something like this should work as a replacement for your entire view definition.
select ID, x.Item
from TempTesting t
cross apply dbo.DelimitedSplit8K(TestData, ',') x
_______________________________________________________________
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/
August 12, 2013 at 6:27 pm
Thanks heaps guys. I'm nearly there now but just getting an error with the CROSS APPLY:
"Msg 321, Level 15, State 1, Line 3
"attributevalue" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90."
Once I solve this hopefully it will all work perfectly.
August 12, 2013 at 8:30 pm
Thank you very much for the help.
I couldn't change the database compatibility level due to a lot of old stuff so I created the view in a new database and then just called this from a view in this database.
It's all working great 🙂
August 12, 2013 at 9:31 pm
jamie 82947 (8/12/2013)
Thank you very much for the help.I couldn't change the database compatibility level due to a lot of old stuff so I created the view in a new database and then just called this from a view in this database.
It's all working great 🙂
Glad you got it working. Most importantly do you understand that code?
_______________________________________________________________
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/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply