TSQL How do I add the same identifying number to all rows in the same group, then increment for the next group, etc

Question

If I have an output dataset from a CTE that looks like

PERIOD  FT  GROUP   DEPT    VALUE
1   Actual  KINDER  MATH    200
2   Actual  KINDER  MATH    363
3   Actual  KINDER  MATH    366
1   Budget  KINDER  MATH    457
2   Budget  KINDER  MATH    60
3   Budget  KINDER  MATH    158
1   Actual  HIGHSCH ENGLISH 456
2   Actual  HIGHSCH ENGLISH 745
3   Actual  HIGHSCH ENGLISH 125
1   Budget  HIGHSCH ENGLISH 364
2   Budget  HIGHSCH ENGLISH 158
3   Budget  HIGHSCH ENGLISH 200
6   Budget  HIGHSCH ENGLISH 502
7   Budget  HIGHSCH ENGLISH 650
1   Actual  COLL    ENGLISH 700
2   Actual  COLL    ENGLISH 540
3   Actual  COLL    ENGLISH 160
1   Budget  COLL    ENGLISH 820
2   Budget  COLL    ENGLISH 630
3   Budget  COLL    ENGLISH 800

but I want to add a column that will have an identifier for each group (the grouping being by FT, Group and Dept) like this:

PERIOD  FT  GROUP   DEPT    VALUE   GroupID 
1   Actual  KINDER  MATH    200 1   
2   Actual  KINDER  MATH    363 1   
3   Actual  KINDER  MATH    366 1   
1   Budget  KINDER  MATH    457 2   
2   Budget  KINDER  MATH    60  2   
3   Budget  KINDER  MATH    158 2   
1   Actual  HIGHSCH ENGLISH 456 3   
2   Actual  HIGHSCH ENGLISH 745 3   
3   Actual  HIGHSCH ENGLISH 125 3   
1   Budget  HIGHSCH ENGLISH 364 4   
2   Budget  HIGHSCH ENGLISH 158 4   
3   Budget  HIGHSCH ENGLISH 200 4   
1   Budget  HIGHSCH ENGLISH 502 5   
2   Budget  HIGHSCH ENGLISH 650 5   
3   Budget  HIGHSCH ENGLISH 336 5   
1   Actual  COLL    ENGLISH 700 6   
2   Actual  COLL    ENGLISH 540 6   
3   Actual  COLL    ENGLISH 160 6   
1   Budget  COLL    ENGLISH 820 7   
2   Budget  COLL    ENGLISH 630 7   
3   Budget  COLL    ENGLISH 800 7

Please do you know how to go about it?

EDIT: I feel like something in this direction may be useful

SELECT *, 
       CASE WHEN FT = 'Actual' THEN <something_incremental_to_do_with_row_num> OVER (PARTITION DEPT, GROUP, FT) END as GROUPID 
FROM cte

I can't use ORDER BY in the OVER clause because I am on 2008

You can (actually you *have* to) use an `ORDER BY` with `row_number()` in SQL Server 2008. You just can't use it with e.g. `sum()` — , Oct 30 '13 at 17:28

score 0 · Answer 1 · edited May 23 '17 at 12:10

0

It's hard to say without seeing the SQL for the query, but I would think a variant on 'row_number() (partition by [some fields])' would give you this. Have you looked into that?

This existing question might give you what you need: How to add sequence number for groups in a SQL query without temp tables

edited May 23 '17 at 12:10

Community

1
1

answered Oct 30 '13 at 14:27

Adam Miller

767
1
9
22

Yes but the variant on rownumber is the bit I dont know. I'm not sure what SQL you'd like to see. The sql for arriving at these outputs? – Uchenna Ebilah Oct 30 '13 at 15:09
I did a quick search, and I edited my answer to show what I found. – Adam Miller Oct 30 '13 at 15:37

score 0 · Answer 2 · edited May 23 '17 at 12:13

Chen, I believe your answer is already available in another thread:

How to return a incremental group number per group in SQL

Additionally, you could just create a unique_id for each group by concatenating fields. For example, if you wanted to group by FT, GROUP, and DEPT, then just put them all together. Ex:

SELECT *, 
       CAST(FT AS VARCHAR) +
       CAST([GROUP] AS VARCHAR) +
       CAST(DEPT AS VARCHAR) AS UNIQUE_ID
FROM MYTABLE

If you wanted to use it for a while to do a more complex query, then just throw it into a temp table:

SELECT *, 
       CAST(FT AS VARCHAR) +
       CAST([GROUP] AS VARCHAR) +
       CAST(DEPT AS VARCHAR) AS UNIQUE_ID
INTO #MYTEMPTABLE
FROM MYTABLE

You only need the CAST function if there are different data types in the fields you want to join. Best to keep it in to avoid any unnecessary headaches. Hope this helps.

TSQL How do I add the same identifying number to all rows in the same group, then increment for the next group, etc

2 Answers2