Change layout of SQL data from table to view

I need to change how data is laid out in a table when I pull it into a view.  I know I can do it the hard with, but I'm hoping someone knows an easier way.

The data is stored like this:
EMPLOYEE - CHEKDATE - PAYTYPE - PAYCODE - HRS - RATE - WAGES
SMITH     04/15/2009     1     401     6.50     11.00     71.50
SMITH     04/16/2009     1     402     2.50    8.00     20.00
JONES     04/15/2009     1     305     6.00     9.00     54.00

I need to show the data in a view like this:
EMPLOYEE - CHEKDATE - 401WAGES - 402WAGES - 305WAGES
SMITH     04/15/2009     71.50     0.00     0.00
SMITH     04/16/2009     0.00     20.00     0.00
JONES     04/15/2009     0.00     0.00     54.00  

I suppose in other words, I need to turn the rows of data into columns of data.  I know I can put in tons of case statements, but there are about 40 different paycodes that would need to turn into columns.  I am hoping someone knows of a way to find all the paycodes and make this easier somehow.

This is a Microsoft SQL Server 2005 database.

Thanks!!!
pmasseycpaAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you could use PIVOT/TRANSFORM statement, however, you still have either to hardcode the list of values, or write some dynamic sql to build the statement for you. you can choose your poison :)
0
 
pmasseycpaAuthor Commented:
Anything else?  I'd rather not go through SSIS if I don't have to.
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.