Subhro, you need to first strip the occurence "cnt" using string functions like "STUFF" or "REPLACE" or "SUBSTRING" and then type-cast. Here is a code that will produce the result you were wanting.
I strongly recommend you try to understand the code ; If not please tel us here, we will explain what my code does. As you are doing an exercise and self-leaning, i am pretty reluctant to post this code, but still, i dont want you to stuck up with a minor hiccup and thats why i am publishing the apt code :
Here's the code!
declare @table table
(empid int, cnt2007 int, cnt2008 int, cnt2009 int)
insert into @table
select 1, 1, 1, 1
union all
select 2, 1, 2 ,1
union all
select 3, 2, 0, 2
select empid, cast((replace([Year], 'cnt','')) as int) as orderyear, value as numorders From
( select * from @table ) UNPIVOT_TABLE
unpivot
(value for [Year] in (cnt2007,cnt2008,cnt2009)) UNPIVOT_handle
where value <> 0
For more on those string functions, Click on the following :
Cheers!