Returnung Columns Based On Column Values

I have a table, something like this:
Emp_Code    Hours   UCode   WCode   Date
123          0              8765    03/13/11
123          6.5            8765    03/13/11
123          7.5    T       8765    03/13/11
123          2              7654    03/14/11
123          7.75           8765    03/14/11
123         11.25   T       8765    03/14/11
123          6.5            8765    03/15/11
123          8.75           8765    03/16/11
123          7              8765    03/17/11
123          5.5            8765    03/18/11
123          5.25           8765    03/19/11
123          0              8765    03/20/11
123          6.25           8765    03/20/11
123          6.5            8765    03/22/11
123          4.25   T       8765    03/23/11
123         11.25           8765    03/23/11
123          7.25   T       8765    03/24/11
123          9.25           8765    03/24/11
123          3.75           7654    03/25/11
123          4.25   T       8765    03/25/11
123          8.5            8765    03/25/11
123          8.75           8765    03/26/11
123          8.75   T       8765    03/26/11

Open in new window


What I need is a query that allows me to return the Emp_Code and three "Hours" columns based on the values contained in the UCode and WCode columns.  

If the UCode column = 'T', then return the value in the Hours column in a column called "Travel".  
If the WCode column = 7654 Then return the value in the Hours column in a column called "Training"
If none of the above are true, return the value in the Hours column in a column called "Regular"

The result will look something like this:
Emp_Code    Hours   UCode   WCode   Date      Regular  Travel  Training
123          0              8765    03/13/11   0.00     0.00    0.00
123          6.5            8765    03/13/11   6.50     0.00    0.00
123          7.5    T       8765    03/13/11   0.00     7.50    0.00
123          2              7654    03/14/11   0.00     0.00    2.00
123          7.75           8765    03/14/11   7.75     0.00    0.00
123         11.25   T       8765    03/14/11   0.00    11.25    0.00
123          6.5            8765    03/15/11   6.50     0.00    0.00
123          8.75           8765    03/16/11   8.75     0.00    0.00
123          7              8765    03/17/11   7.00     0.00    0.00
123          5.5            8765    03/18/11   5.50     0.00    0.00
123          5.25           8765    03/19/11   5.25     0.00    0.00
123          0              8765    03/20/11   0.00     0.00    0.00
123          6.25           8765    03/20/11   6.25     0.00    0.00
123          6.5            8765    03/22/11   6.50     0.00    0.00
123          4.25   T       8765    03/23/11   0.00     4.25    0.00
123         11.25           8765    03/23/11  11.25     0.00    0.00
123          7.25   T       8765    03/24/11   0.00     7.25    0.00
123          9.25           8765    03/24/11   9.25     0.00    0.00
123          3.75           7654    03/25/11   0.00     0.00    3.75
123          4.25   T       8765    03/25/11   0.00     4.25    0.00
123          8.5            8765    03/25/11   8.50     0.00    0.00
123          8.75           8765    03/26/11   8.75     0.00    0.00
123          8.75   T       8765    03/26/11   0.00     8.75    0.00

Open in new window


I must be able to filter on the date, and the query will be used in Crystal Reports, so there can only be one WHERE clause.

TIA
LVL 10
ClifAsked:
Who is Participating?
 
Alpha AuConnect With a Mentor Commented:
try using the case..when..then..else..end
select 
 Emp_Code    
,Hours   
,UCode  
,WCode   
,Dat
,case when Ucode = 'T' then Hours end as Travel
,case when WCode = 7654 then Hours end as Training
,case when Ucode = 'T' and WCode = 7654 then Hours end as Regular
from table

Open in new window

0
 
Ephraim WangoyaCommented:
try
select Emp_Code, case 
                   when UCode = 'T' then
                     Travel
                   when WCode = 7654 then
                     Training
                   else 
                     Regular
                 end [Hours  ]
from   YourTable
where DATEADD = YourDate

Open in new window

0
 
Shaun KlineLead Software EngineerCommented:
Just a cleanup of alphaau's comment:
select 
 Emp_Code    
,Hours   
,UCode  
,WCode   
,Dat
,case when Ucode <> 'T' and WCode <> 7654 then Hours else 0.00 end as Regular
,case when Ucode = 'T' then Hours else 0.00 end as Travel
,case when WCode = 7654 then Hours else 0.00 end as Training
from table

Open in new window

0
 
ClifAuthor Commented:
That seems to work, thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.