newbie: convert tinyint to string conditionally? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

newbie: convert tinyint to string conditionally?

Author

Topic

DTFan
Yak Posting Veteran

52 Posts

Posted - 2005-12-06 : 13:44:38

Hey everyone, more questions from the newbie :) I am hoping this is an easy one.

I'm using MSDE 2000 and VB.NET 2003. I've got a table that has the following fields:

tblStudent
studID  (int p/k)
studFName  (varchar (25))
studLName  (varchar (35))
studEmail  (varchar (75))
studAddr1  (varchar (35))
studAddr2  (varchar (25))
...
...
...
studCurr  (bit (default = 0))
studMeet  (bit (default = 0))
studType  (tinyint (default = 1))

Basically it stores student information. studCurr is a bit to indicate if they are a current student (0 = no, 1 = yes). studMeet is a bit to indicate whether they have had their semester "meeting" with their counselor (0 = no, 1 = yes). studType is a tinyint which indicates what type of student they are (1 = full time, 2 = part time, 3 = grad student, 4 = night).

I'm running a stored procedure:

CREATE PROCEDURE admin_sp_GetStudentList

AS

SET NOCOUNT ON

BEGIN

     SELECT    studID, studFName, studLName,
               studEmail, studMeet, studType
     FROM      tblStudent
     WHERE     studCurr = 1 
     ORDER BY  studType, studLName, studFName
END

I want the stored procedure to return all students in the table who are "current" students. Here's what that would return right now (as an example):

quote:

studID|studFName|studLName|studEmail|studMeet|studType
27|Jordana|Brewster|jb@fastandfurious.com|1|1
34|Kiera|Knightley|kiera@thejacket.com|1|1
12|Ali|Larter|ali@finaldestination.com|0|1
93|Jennifer|Morrison|jen@house.com|0|1
178|Evangeline|Lilly|elilly@lost.com|0|2
95|Rhona|Mitra|rhona@niptuck.com|1|2
53|Jennifer|Finnigan|jfinn@closetohome.com|1|3

I'm going to list the students in a datagrid but here's my problem. Instead of the column showing 1 or 0 for studMeet I'd like it to be a "Yes" or "No". And the same goes for studType. Instead of showing 1, 2, 3 or 4 (no four present in my example data), I'd like it to say "Full Time", "Part Time", "Grad", "Night". So it should look like this:

quote:

studID|studFName|studLName|studEmail|studMeet|studType
27|Jordana|Brewster|jb@fastandfurious.com|Yes|Full Time
34|Kiera|Knightley|kiera@thejacket.com|Yes|Full Time
12|Ali|Larter|ali@finaldestination.com|No|Full Time
93|Jennifer|Morrison|jen@house.com|No|Full Time
178|Evangeline|Lilly|elilly@lost.com|No|Part Time
95|Rhona|Mitra|rhona@niptuck.com|Yes|Part Time
53|Jennifer|Finnigan|jfinn@closetohome.com|Yes|Grad

Notice it's also grouping (through the Order By clause) the studType field? That's because I would like all full time students listed first, then part time, then grad, then night students.

Any idea's? I'm lost on this one. I know (and I use that word loosely) that I have to do a convert (or at least I think I do), but I'm not 100% sure on how and, can you run a if/else or a select case inside of a select? If so, how?

Sorry if this is a real basic question but I've been beating my head against the wall for awhile now and could really use some help. Thanks in advance for any help/suggestions you can give.

PS - Sorry about the bad formatting on the example data area's. I couldn't figure out how to get something like [pre][/pre] to work. Since spacing wasn't an option I opted for the pipe symbol.

DTFan
Ever-hopeful programmer-in-training

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-06 : 14:47:47

2 options for studType:

a) is there a studType table you can join to? (there should be)


select a.studid
       ,<otherColumns>
       ,b.studTypeDesc
from   tblStudent a
join   tblStudentType b
          on b.studTypeid = a.StudTypeid

b) and this will work for the bit columns as well:


select <otherColumns>
       ,case 
        when studType = 1 then 'full time'
        when studType = 2 then 'part time'
        when studType = 3 then 'grad student'
        when studType = 4 then 'night'
       end as [Student Type]
from...

for future posts, generate a script to create the table and paste it in your post. then create some insert statements for your sample data. With that we can provide a working solution.

Be One with the Optimizer
TG

DTFan
Yak Posting Veteran

52 Posts

Posted - 2005-12-06 : 15:34:00

quote:
Originally posted by TG

2 options for studType:

a) is there a studType table you can join to? (there should be)

select a.studid
,<otherColumns>
,b.studTypeDesc
from tblStudent a
join tblStudentType b
on b.studTypeid = a.StudTypeid

Be One with the Optimizer
TG

I thought about adding the table for studType and will probably do so. But I did want to see how it could be done if it wasn't there. But yeah, a Join would definitely be the way to go.

quote:

Originally posted by TG


select <otherColumns>
       ,case 
        when studType = 1 then 'full time'
        when studType = 2 then 'part time'
        when studType = 3 then 'grad student'
        when studType = 4 then 'night'
       end as [Student Type]
from...

Be One with the Optimizer
TG

SWEET! Thank you for that information. I'll be putting that into my stored procedure as soon as I get home. Seeing the Case statement work like that is very beneficial in trying to figure this stuff out (or see how it works so I'll be able to figure this stuff out in the future

quote:
Originally posted by TG
for future posts, generate a script to create the table and paste it in your post. then create some insert statements for your sample data. With that we can provide a working solution.

Be One with the Optimizer
TG

Creating a script for the database and the data never even occurred to me (and I'm sure you're completely surprised by that, huh?

But I will keep that in mind for all future posts (which, I can promise, there will be many) :)

Thanks again for the help/suggestions/tutorial. It is very much appreciated.

DTFan
Ever-hopeful programmer-in-training

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-06 : 15:41:34

You're quite welcome! I know it's hard to get started with a new technology. I hope you're spending a lot of time paroosing and referencing sql server's Books Online. Lots of good info and examples.

Be One with the Optimizer
TG

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-06 : 16:39:19

I strongly recommend creating a table and joining to that. The CASE should only be used as a last resort for things like this. Since you are working with a relational database, use the features that joins and related tables give you ! If you ever have a 5th type, or if you want to change the descriptions of any of those, you will need to edit the CASE statements in all of your SQL to make the changes, as opposed to simply editing the contents of a table.

I only bring this up because you mention that you are starting off in sql server, and I want to help you develop good habits from the get-go.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources