• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

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!!!
0
pmasseycpa
Asked:
pmasseycpa
1 Solution
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now