SQLTeam.com | Weblogs | Forums

Assigning a global reference alphabet to results


#1

I have query which returns user name, user zip code and 20+ other fields based on their IDs. For every data type returned, i cannot display the name as it gets redundant. I am trying to find a way to assign a global alphabet to every record returned. For instance; for student ID 1, i need to assign 'A', for student ID 223, i need to assign 'B', so on and so forth.
Here's a sample query:

select STUFF((SELECT', '+CAST(name AS VARCHAR)+', '+CAST(dob AS VARCHAR)from dbo.students FOR XML PATH('')),1,1,'')

I am hoping to get A) Chris,05/15/1985 B) Jeff,06/12/1982 C) Jordan, 09/04/1986


#2

What should happen when you have more than 26 rows in your data?

The following will do something like what you are asking, but it will roll over and start with A again at the 27th row.

select STUFF((SELECT', '+
-- this is the added coded
CHAR( (ROW_NUMBER() OVER (ORDER BY name )-1)%26 + 65 ) + ')' +
-- end of added code
CAST(name AS VARCHAR)+', '+CAST(dob AS VARCHAR)from dbo.students FOR XML PATH('')),1,1,'')