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.