Help creating a View

  • 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?

  • Oops sorry. I just noticed I put this into SQL Server 2008 instead of 2005. Can someone move it please?

  • 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

  • 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.

  • 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?

  • 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[/url]

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 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/

  • 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.

  • 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 🙂

  • 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