Post reply

Manipulate Data on table without using Cursor or while loop

  • March 25, 2010 at 6:02 am

    #231129

    I have a table #demo like below

    Create table #demo

    (slno int, linecount int, pageno int, colno int, startrow int)

    Insert into #demo(Slno, linecount)

    Select 1,9 union all

    Select 2,12 union all

    Select 3,12 union all

    Select 4,14 union all

    Select 5,12 union all

    Select 6,10 union all

    Select 7,10 union all

    Select 8,11 union all

    Select 9,9 union all

    Select 10,10 union all

    Select 11,7 union all

    Select 12,11 union all

    Select 13,11 union all

    Select 14,12 union all

    Select 15,9 union all

    Select 16,11 union all

    Select 17,11 union all

    Select 18,11 union all

    Select 19,10 union all

    Select 20,10 union all

    Select 21,10 union all

    Select 22,11 union all

    Select 23,11 union all

    Select 24,14 union all

    Select 25,9

    Without using cursor or while loop I am trying to populate the pageno, colno and startrow fields

    Rules are that the First record will have the pageno as 1, colno as 1 and startrow as 1

    The colno of each record will move between 1 , 2 , 3 and 4

    The startrow will be the sum of startrow + linecount of the previous row with the same colno and same pageno

    The colno of the current record (identified by the slno column) will be one more than the colno of previous record (subject to a maximum of 4)as long as the sum of the startrow + linecount of the current record is not more 50. If it is more than 50 then the colno will move one more (again subject to the maximum of 4 and startrow + linecount is not more than 50) and so on......

    If all colno have reached the 50 limit than the pageno will increment by 1 and the colno and the startrow will get reset to 1

    Please let me know if any of you have any solution to this problem.

  • March 25, 2010 at 8:07 am

    #1139655

    It would be really helpful if, based on the sample data provided (BTW, thanks for that!), if you would supply what the expected output would be.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • March 25, 2010 at 8:09 am

    #1139657

    The output will be something like below -

    Slno LinecountPageNoColnoStartrow

    19111

    212121

    312131

    414141

    5121110

    6101213

    7101313

    8111415

    991122

    10101223

    1171323

    12111426

    13111131

    14121233

    1591330

    16111437

    17111339

    1811211

    1910221

    2010231

    2110241

    22112112

    23112211

    24142311

    2592411

  • March 25, 2010 at 8:22 am

    #1139665

    Okay, now show us what you have tried so far to solve your problem.

Viewing 4 posts - 1 through 4 (of 4 total)

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