SQL Server Update Loop/Cursor

Question

Index   Columns         Text          New Index
===========================================
1   ISOCountry  TH          1
-------------------------------------------
2   Country        Thailand         1
-------------------------------------------
3   Region         Asia         1
-------------------------------------------
4   Date           12/31/2014       1
-------------------------------------------
5   Holiday        New Years Eve    1
-------------------------------------------
7   ISOCountry     DE           2
-------------------------------------------
8   Country        Germany          2
-------------------------------------------
9   Region         EMEA             2
-------------------------------------------
10  Date           12/31/2014       2
-------------------------------------------
11  Holiday        New Years Eve    2
-------------------------------------------
13  ISOCountry  DK          3
-------------------------------------------
14  Country         Denmark         3
-------------------------------------------
15  Region          EMEA            3
-------------------------------------------
16  Date            12/31/2014  3
-------------------------------------------
17  Holiday         New Years Eve   3
-------------------------------------------

I have a column as above. I need a loop/cursor in SQL Server such that everytime the Column "Column" changes to ISOCountry, the New Index column has to be incremented by 1. Which means that for Rows 1 through 5 the New Index column has a value 1, for rows 7 through 11 the New Index value has to be 2 and so on. The point where the Columns changes to ISO Country the New Index Value has to change to 2 likewise for the rest of the columns. I will have about 10000 rows in all

Any help in this regard will be greatly appreciated. Thanks

Why on earth would you store data that way? That is an extremely opoor table design. — HLGEM
– HLGEM, Commented Mar 28, 2014 at 19:01
We are getting data from a file in this format... I will use this to convert the table to Transpose the records — NottyHead
– NottyHead, Commented Mar 28, 2014 at 19:20

CElliott · Accepted Answer · 2014-03-28 19:13:24Z

1

This will set the newIndex based on the number of ISOCountry records prior to the current record:

UPDATE A
SET newIndex = B.newIndex
FROM [myTable] A
CROSS APPLY (
    SELECT COUNT(*) [newIndex]
    FROM [myTable] X
    WHERE   X.[Index] <= A.[Index]
        AND X.[Columns] = 'ISOCountry'
) B

edited Mar 28, 2014 at 19:13

answered Mar 28, 2014 at 19:01

CElliott

4522 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2014-03-28 18:59:33Z

1

Why use a loop? Just set the newindex value to the count of isocountry records with a lower id. You can do this with a correlated subquery (as well as with other methods):

update table t
    set newindex = (select count(*)
                    from table t2
                    where t2.columns = 'IsoCountry' and
                          t2."index" <= t."index"
                   );

answered Mar 28, 2014 at 18:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL Server Update Loop/Cursor

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related