Create view or table with variable columns based on transaction data

[timroop]

I have a table with transaction data that includes an employee id,check date, code name, and a code ammount. The basic structure is similar to the temp table below. It will include other columns but they are not important to the report.

Declare @Temp Table(EmpId VarChar(20), Checkdate smalldatetime, Code varchar(10), CodeAmt numeric(19,5))
Insert Into @Temp Values('01123DOE', '01/01/2001', 'Medical', 10.00)
Insert Into @Temp Values('01123DOE', '01/01/2001', 'Dental', 20.00)
Insert Into @Temp Values('01123DOE', '01/01/2001', 'Hours', 30.00)
Insert Into @Temp Values('01123DOE', '02/01/2001', 'Medical', 10.00)
Insert Into @Temp Values('01123DOE', '02/01/2001', 'Dental', 20.00)
Insert Into @Temp Values('01123DOE', '02/01/2001', 'Hours', 30.00)
Insert Into @Temp Values('01123FOP', '02/01/2001', 'Dental', 40.00)
Insert Into @Temp Values('01123FOP', '01/01/2001', 'Salary', 50.00)
Insert Into @Temp Values('01123FOP', '01/01/2001', '401k', 60.00)
Insert Into @Temp Values('01123FOP', '02/01/2001', 'Salary', 50.00)
Insert Into @Temp Values('01123FOP', '02/01/2001', '401k', 60.00)
Insert Into @Temp Values('01123JIM', '01/01/2001', 'Hours', 70.00)
Insert Into @Temp Values('01123JIM', '01/01/2001', 'Msc_Reimb', 80.00)
Insert Into @Temp Values('01123JIM', '01/01/2001', 'Toll', 90.00)
Insert Into @Temp Values('01123JIM', '02/01/2001', 'Hours', 70.00)
Insert Into @Temp Values('01123JIM', '02/01/2001', 'Msc_Reimb', 80.00)
Insert Into @Temp Values('01123JIM', '02/01/2001', '401k', 90.00)

I need to generate a report/table/view that lists each employees checks by date with a column for each code. An example based on the above table is below.

EMPID	  Checkdate   Medical  Dental  Hours  Salary  401k  Msc_Reimb  Toll
01123DOE  2001-01-01  10       20      30
01123DOE  2001-02-01  10       20      30
01123FOP  2001-01-01           20             50      60
01123FOP  2001-02-01           20             50      60
01223JIM  2001-01-01                   70                   80         90
01223JIM  2001-02-01                   70             90    80

The difficulty is that I need a column for each code in the transaction list. The included codes will be different each time the report is run.

Is this possible? I know a way to do it with unions, but only when the code names are the same from run to run. With this new dataset the codes will change.

Thanks.

Tim

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

 

[RiverGuy]

A view needs to have a consistent column structure. You could throw together some dynamic SQL in a stored procedure to output a variable result set. But it's better to format the data as you want in a reporting tool utilizing a crosstab/matrix/pivot table