Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help creating a View


Help creating a View

Author
Message
jamie 82947
jamie 82947
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 31
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?
jamie 82947
jamie 82947
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 31
Oops sorry. I just noticed I put this into SQL Server 2008 instead of 2005. Can someone move it please?
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16461 Visits: 13207
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

This function will knock the socks off the cursor and it will give you an easy way to use it in your queries.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
jamie 82947
jamie 82947
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 31
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.
jamie 82947
jamie 82947
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 31
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?
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16461 Visits: 13207
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.

SQL Server APPLY Basics



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16616 Visits: 17024
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 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)
jamie 82947
jamie 82947
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 31
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.
jamie 82947
jamie 82947
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 31
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 Smile
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16616 Visits: 17024
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 Smile


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 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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search