CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Haminteu
Haminteu

Reputation: 1334

Computed Column in SQL server 2008 table

How can I create a computed column like the following table?? 

ID Group Name Age myComputedColumn
-----------------------------------
1  001   FF   28  00
2  001   GG   29  01
3  001   II   35  02
4  002   AA   15  00
5  001   UU   26  03
6  002   BB   17  01
7  002   CC   15  02

In myComputedColumn, I would like to have value starting at '00' up to '24', after the row reaches '24', then it will create a new number which is '00' until '24'

How can I create a computed column like this? Or maybe I need to create a function when inserting a new record?

Thank you.

Upvotes: 0

Views: 69

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269533

If you want the number to go from 0 to 24, then you would use:

select t.*,
       (row_number() over (partition by [group] order by id) - 1) % 25
from table t;

The % operator is the modulus operator. It returns the remainder after integer division.

To get this as a character, the right()/append 0 trick works fine:

select t.*,
       right('0' + cast((row_number() over (partition by [group] order by id) - 1) % 25 as varchar(3)), 2) as ComputedColumn
from table t;

Upvotes: 4

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

This will give value statrting from 1,2,3 and so on..

Select ID,Group,Name,Age,
       ROW_NUMBER() OVER (PARTITION BY [Group] ORDER BY ID) as computedcolumn
FROM tableName

You can change if want to start from zero , subtract by 1 

Select ID,Group,Name,Age,
       ROW_NUMBER() OVER (PARTITION BY [Group] ORDER BY ID) - 1 as computedcolumn
FROM tableName

Ok , so you want padded zeros also , you can do using RIGHT()

Select ID,Group,Name,Age,
       RIGHT('0' + ROW_NUMBER() OVER (PARTITION BY [Group] ORDER BY ID) - 1 , 2) as computedcolumn
FROM tableName

Upvotes: 1

Related Questions