Manipulate data without using Cursor or While Loop

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Manipulate data without using Cursor or While Loop

Author

Topic

ts_giri
Starting Member

3 Posts

Posted - 2010-03-25 : 07:59:51

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.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-25 : 08:03:26

could you please show us the output expected?

ts_giri
Starting Member

3 Posts

Posted - 2010-03-25 : 08:11:27

The output will be like below

Slno Linecount PageNo Colno Startrow
1 9 1 1 1
2 12 1 2 1
3 12 1 3 1
4 14 1 4 1
5 12 1 1 10
6 10 1 2 13
7 10 1 3 13
8 11 1 4 15
9 9 1 1 22
10 10 1 2 23
11 7 1 3 23
12 11 1 4 26
13 11 1 1 31
14 12 1 2 33
15 9 1 3 30
16 11 1 4 37
17 11 2 1 1
18 11 2 2 1
19 10 2 3 1
20 10 2 4 1
21 10 2 1 12
22 11 2 2 12
23 11 2 3 11
24 14 2 4 11
25 9 2 1 22

ts_giri
Starting Member

3 Posts

Posted - 2010-03-25 : 08:18:02

Small correction to the expected output -

Slno Linecount PageNo Colno Startrow
1 9 1 1 1
2 12 1 2 1
3 12 1 3 1
4 14 1 4 1
5 12 1 1 10
6 10 1 2 13
7 10 1 3 13
8 11 1 4 15
9 9 1 1 22
10 10 1 2 23
11 7 1 3 23
12 11 1 4 26
13 11 1 1 31
14 12 1 2 33
15 9 1 3 30
16 11 1 4 37
17 11 1 3 39
18 11 2 1 1
19 10 2 2 1
20 10 2 3 1
21 10 2 4 1
22 11 2 1 12
23 11 2 2 11
24 14 2 3 11
25 9 2 4 11

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-25 : 08:41:52

I'm not sure you'll be able to do this without a cursor type statement, because the values you put in later rows depend on the values you put in earlier rows. eg, the values updated in row 5 depend on the values updated in row 1. I believe this means that you have to issues separate update statements, because using a single update statement, there's no guarantee which row would be updated first.

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources