I'm trying to create a query which will select a team from a declared variable and then make the remaining teams "anonymous" by giving them generic brands and sequential IDs.
For example, if my dataset has 3 different team names (ABC, DEF, and GHI), but I would only like the true value of the 1 chosen team (ABC) displayed. Here is the skeleton of the query I'm working with:
SET @teamid = 123; SELECT CASE WHEN ID = @teamid THEN NAME ELSE 'Team' + ' - ' + SEQ END AS 'Team Name', SUM(TOTAL) AS 'Team Total' FROM TEAM GROUP BY 1;
I would like the results to look something like this:
Team Name: Team Total: ABC 100 Team - 1 50 Team - 2 150
How can I go about creating a query which will create a unique number that I can replace the original team name with? I know I have to replace the "SEQ" portion of the case statement, but I'm not sure what exactly to replace it with. Also, it is important that each team has the same ID whether or not it is anonymous (so if team DEF has 50 rows, it should be shown as Team - 1 only as opposed to Team - 1-50) so that my groupings will work properly.
Thanks for the help.