Best method to split columns into rows?

Question

What would be the best method to split columns into rows (like matrix?) Example table:

Name Hours1 Hours2 Hours3
Jon  32     40     30
Ana  40     0      40

Result (don't show 0 values):

Name Hours
Jon  32
Jon  40
Jon  30
Ana  40
Ana  40

One way I can think of doing this is by using Union

SELECT
  Name,
  Hours1
FROM #HOURSTABLE
WHERE Hours1 <> 0

UNION ALL

SELECT
  Name,
  Hours2
FROM #HOURSTABLE
WHERE Hours2 <> 0

UNION ALL

SELECT
  Name,
  Hours3
FROM #HOURSTABLE
WHERE Hours3 <> 0

Any other suggestion?

Answer 1

I prefer the CROSS APPLY for such items. It offers a bit more flexibility.

Example

Select Name
      ,B.*
 From  YourTable A
 Cross Apply ( values (Hours1)
                     ,(Hours2)
                     ,(Hours3)
             ) B(Hours)
 Where B.Hours<>0

Returns

Name    Hours
Jon     32
Jon     40
Jon     30
Ana     40
Ana     40

Answer 2

One approach would be to use unpivot

https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

 declare @t table (name varchar(50), hours1 int, hours2 int, hours3 int)

 insert @t 
 select 'Jon ', 32     ,40     ,30
 union
 select 'Ana  ',40     ,0      ,40

 select name, hours from @t
 unpivot (hours for h in (hours1, hours2, hours3)) u
 where hours<>0

The full results from unpivot mean that you don't lose the information about which column the hours value was in (ie: hours1, hours2, hours3)