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

Help creating a View Expand / Collapse
Author
Message
Posted Thursday, August 8, 2013 9:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 6:07 PM
Points: 8, Visits: 27
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?

Post #1482631
Posted Thursday, August 8, 2013 10:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 6:07 PM
Points: 8, Visits: 27
Oops sorry. I just noticed I put this into SQL Server 2008 instead of 2005. Can someone move it please?
Post #1482633
Posted Friday, August 9, 2013 12:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 13,520, Visits: 11,313
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1482660
Posted Sunday, August 11, 2013 5:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 6:07 PM
Points: 8, Visits: 27
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.
Post #1483119
Posted Sunday, August 11, 2013 6:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 6:07 PM
Points: 8, Visits: 27
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?
Post #1483126
Posted Monday, August 12, 2013 2:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:00 AM
Points: 13,520, Visits: 11,313
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1483191
Posted Monday, August 12, 2013 7:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
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)
Post #1483318
Posted Monday, August 12, 2013 6:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 6:07 PM
Points: 8, Visits: 27
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.
Post #1483519
Posted Monday, August 12, 2013 8:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 6:07 PM
Points: 8, Visits: 27
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 :)
Post #1483536
Posted Monday, August 12, 2013 9:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
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 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 #1483543
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse