Add a specific counter column to a SQL Server result

user_10547516 0
Viewed 87

I have a table in SQL Server like this:

Name | age
-----+-------
A    |   24
B    |   32
C    |   25
D    |   32
F    |   24
G    |   32
H    |   45
...

How can I add a column that repeats a value n times and then increments 1

Example: (n = 3)

New Column |  Name | age
-----------+-------+-------
1          |   A   |    24
1          |   B   |    32
1          |   C   |    25
2          |   D   |    32
2          |   F   |    24
2          |   G   |    32
3          |   H   |    45
...

Thank you.

tsql sql-server sql
4 Answers

I would simply use arithmetic on row_number():

select (row_number() over (order by name) + 2) / 3 as new_column,
       t.*
from t;
user_11440350

This suggests me row_number() & dense_rank() :

select dense_rank() over (order by (seq1-seq2)) as newcol, Name, Age
from (select t.*, row_number() over (order by name) as seq1,
                 (row_number() over (order by name) - 1) % 3 as seq2
      from table t
     ) t;
user_55294450

Just another option

Example

Select NewCol = sum(case when RN=1 then 1 end) over (Order by Name)
      ,Name
      ,Age
 From (
        Select *,RN = Row_Number() over (Order by [Name]) % 3
         from @YourTable
      ) A

Returns

NewCol  Name    Age
1       A       24
1       B       32
1       C       25
2       D       32
2       F       24
2       G       32
3       H       45
user_15700000

Here's another option. Just use ROW_NUMBER and some math. Here's sample code where you can define/change the number of times you want it to repeat based on a variable.

DECLARE @TestData TABLE
    (
        [Name] NVARCHAR(2)
      , [Age] INT
    );


--Set your repeating amount here
DECLARE @Repeat INT = 3;

INSERT INTO @TestData (
                          [Name]
                        , [Age]
                      )
VALUES ( 'A', 24 )
     , ( 'B', 32 )
     , ( 'C', 25 )
     , ( 'D', 32 )
     , ( 'F', 24 )
     , ( 'G', 32 )
     , ( 'H', 45 )
     , ( 'I', 22 )
     , ( 'J', 22 )
     , ( 'K', 22 )
     , ( 'L', 22 )
     , ( 'M', 22 )
     , ( 'N', 22 );

SELECT   ( ROW_NUMBER() OVER ( ORDER BY [Name] ) + ( @Repeat - 1 )) / @Repeat AS [New Column]
       , *
FROM     @TestData
ORDER BY [Name];
user_103765370
Related