Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
epoppleton
Starting Member
5 Posts |
 Posted - 2009-10-28 : 12:28:41
|
| I need to generate a group ID on the fly.For example, if I had 2 users named Bill, 2 named Tom and 3 named Janet, I need to generate id's for each group of names. How can I do this in sql? Name | IDBill | 1Bill | 1Tom | 2Tom | 2Janet | 3Janet | 3Janet | 3 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-28 : 12:36:06
|
Something like this:select t1.Name,dt.groupIdfrom your_Table t1join(select Name, row_number() over (partition by Name order by Name) as groupId from your_Table group by Name)dton dt.Name = t1.Name
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
epoppleton
Starting Member
5 Posts |
Posted - 2009-10-28 : 13:24:55
|
| Thanks Webfred, I have a table called Business, and I tried your suggestion on the field BusinessName like this: selectt1.BusinessName,dt.groupIdfrom tblBusiness t1join(select BusinessName, row_number() over (partition by BusinessName order by BusinessName) as groupId from tblBusiness group by BusinessName)dton dt.BusinessName = t1.BusinessNameThe ID's all came back as "1". Did I do something wrong? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-28 : 13:36:43
|
Oh I'm sorry!remove that: "partition by BusinessName "
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
epoppleton
Starting Member
5 Posts |
Posted - 2009-10-28 : 13:38:25
|
| Worked beautifully, thank you so much. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-28 : 13:41:31
|
welcome 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
epoppleton
Starting Member
5 Posts |
Posted - 2009-10-28 : 17:28:35
|
| Webfred, let me add to this and see if you can help. If I then wanted to add another column to my results that then numbered sequentially the groupid, how would I do that? i.e.Name | ID | SeqBill | 1 | 1Bill | 1 | 2Tom | 2 | 1Tom | 2 | 2Janet | 3 | 1Janet | 3 | 2Janet | 3 | 3 |
|
|
|
epoppleton
Starting Member
5 Posts |
Posted - 2009-10-28 : 19:16:23
|
| Nevermind, I got it. Thanks again for your help.selecttblbusiness.businessname,dt.ref,row_number() over (PARTITION BY ref ORDER BY ref) as 'seq'from tblbusinessjoin(select businessname, row_number() over (order by businessname) as ref from tblbusiness group by businessname) dton dt.businessname = tblbusiness.businessname |
|
|
|
|
|
|
|
|
Generate Group ID's