Search code examples
sqlsql-serverexcelauto-increment

SQL Script to Auto Increment Depending on Column Value

This may be tricky. I have an xlsx file with sets of rows that have one similar column value. The issue is that the client did not give me the correct values for another column (the column does not exist). I need to auto increment a new column based on the value in the other column. When the value of the column changes, the incremented var needs to go back to 0.

EX:

Current
C1 | C2 | 
1  | A  |
2  | A  |
3  | A  |
4  | B  |
5  | B  |

Desired
C1 | C2 | C3 (NEW)
1  | A  | 0
2  | A  | 1
3  | A  | 2
4  | B  | 0
5  | B  | 1

Is it possible to do this through SQL? Is there a better way to do this in Excel or Access?

Solution

  • This is very possible with SQL, here is a SQL Fiddle, and here is the content of that fiddle:

    CREATE TABLE a
(
  C1 INT,
  C2 VARCHAR(10)
)

GO

INSERT INTO a VALUES (1, 'A')
INSERT INTO a VALUES (2, 'A')
INSERT INTO a VALUES (3, 'A')
INSERT INTO a VALUES (4, 'B')
INSERT INTO a VALUES (5, 'B')

SELECT C1, C2,
  ROW_NUMBER() OVER (PARTITION BY C2 ORDER BY C1) - 1 AS C3
FROM a