0

Is it possible to add a group name to a grouped set in T-SQL

Select Class, Category, Gender,Count(1) as Total, 'GroupNameHere' from PersonTable
Group By Class, Category, Gender

I want that every grouped set is given a new name like mentioned below

Actual Table

Name     |    Class   |   Category   |   Gender   |   Address   |   ZipCode
----------------------------------------------------------------------------
ABC      |      2     |     Cat 1    |     M      |  Some Add.. |   10125
XYZ      |      2     |     Cat 1    |     M      |  Some Add.. |   20554
AFF      |      3     |     Cat 3    |     M      |  Some Add.. |   24525
ASD      |      7     |     Cat 6    |     F      |  Some Add.. |   18892
GDS      |      7     |     Cat 6    |     F      |  Some Add.. |   17745

Output

Class |   Category   |   Gender   |   Total   |   GroupName
----------------------------------------------------------------------------
2     |     Cat 1    |     M      |     2     |   Group 1
3     |     Cat 3    |     M      |     1     |   Group 2
7     |     Cat 6    |     F      |     2     |   Group 3
Cyberpks
  • 1,401
  • 6
  • 21
  • 51

2 Answers2

2
Select Class, Category, Gender,Count(1) as Total,  'Group ' +CAST (Row_Number() over ( order by Class, Category, Gender) as varchar(10) )as GroupName 
from PersonTable 
Group By Class, Category, Gender
Smog
  • 625
  • 6
  • 19
  • OMG, that was so simple... How can I forget `Row_Number()` for this purpose? Thanks for waking me up :D. – Cyberpks Feb 26 '15 at 06:06
0

Got the solution by referring this. I can use NewId() to generate a GUID based row id.

Select Class, Category, Gender,Count(1) as Total, 
'Group' + Convert(varchar(100),(ABS(CHECKSUM(NewId())))) as GroupName from PersonTable
Group By Class, Category, Gender
Community
  • 1
  • 1
Cyberpks
  • 1,401
  • 6
  • 21
  • 51