Post reply

Want a solution on cursor problem

  • August 24, 2011 at 6:54 am

    #246865

    Hi All,

    I am in trouble with cursor problem, please give me a hint, ideas or links to solve it.

    I want to compare each cell bye cell for tablename column and required to blank the cell and rows wherevere it required.

    Please find attached Excel attachment for detail table structure.

    i have tried till to fetch each row using cursor.

    Declare @TableName varchar(100)

    Declare @ColName varchar(100)

    Declare Cursor_del Cursor for

    Select Table_Name , Column_Name

    from Dbo.TableName

    Open Cursor_del

    Fetch Next from Cursor_del

    into @TableName, @ColName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    FETCH NEXT FROM Cursor_del

    INTO @TableName, @ColName

    Select @TableName, @ColName

    END

    Close Cursor_del

    Deallocate Cursor_del

    Thanks,

    Prash

  • August 24, 2011 at 7:06 am

    #1372525

    You can probably do this without a cursor, although it would be messy. However, this kind of display formatting is better done in the presentation layer than in the database layer. For example, you can display the results in Excel and then write a simple VB macro do put in the blanks. Or you could use Reporting Services if you already have it set up.

    John

  • August 24, 2011 at 7:07 am

    #1372526

    Oops! Duplicate post.

  • August 24, 2011 at 2:14 pm

    #1372816

    I agree with John, this is a task better left to the display layer.

    That said, in 2005 you can use the row_number to check if you have the first row for a group.

    declare @Foo table(bar char(3), baz char(3));

    insert into @Foo values('foo', 'bar');

    insert into @Foo values('foo', 'baz');

    insert into @Foo values('bar', 'foo');

    insert into @Foo values('bar', 'baz');

    select case when row_number() over (partition by bar order by baz) = 1 then bar else '' end as bar, baz from @Foo

    In 2008 there are the grouping set special values.


    Dan Guzman - Not the MVP (7/22/2010)
    All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

  • August 25, 2011 at 1:33 am

    #1372970

    Thanks John.!! For your kind reply.

    This problem is not related to just presentation. I wanted to create SP on it, which will work for millions of records. And i m not having knowledge about VB Macro in Excel.

    🙂

  • August 25, 2011 at 1:52 am

    #1372975

    Are you really intending to put millions of rows into a spreadsheet?

    May I ask what the business driver is? - there might be a better alternative.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 25, 2011 at 1:56 am

    #1372977

    This problem is not related to just presentation.

    Please will you expand on that? What else is it related to?

    And i m not having knowledge about VB Macro in Excel.

    This brings to mind the old adage about the man with a hammer thinking that everything's a nail. If you come across a screw, learn to use a screwdriver instead of trying to hammer it in.

    With all of that said, if you insist on doing this in the database, you could try one of weitzera's suggestions above.

    John

  • August 27, 2011 at 1:10 am

    #1374196

    Thanks John,

    Will try weitzera's suggestions.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply