Reporting like this

I am using SQL Server 2005 Express Edition.
I have a table whose structure is follows:
Date
EmpID
Code
Amount
Sample data looks like this:
---
Date EMPID Code Amount
---
1-Jul-07 1 A1 100
1-Jul-07 1 B1 100
1-Jul-07 2 A1 150
1-Jul-07 3 B1 50
1-Jul-07 4 C1 120
1-Jul-07 4 D1 80
----
The Codes are not fixed, they can be of any number and varies from
employee to employee.
I want to show a report like this:
Month: Jul-07 -- CODES --
---
EmpID A1 B1 C1 D1
---
1 100 100 0 0
2 150 0 0 0
3 0 50 0 0
4 0 0 120 80
----
Since Codes are not fixed, so an SQL query is difficult. What is the
alternative approach?
I am thinking to create another table at run-time that reads column
values (Code names) from the original table and create a field of that
name in the next table.
I also want to know in which edition of SQL Server is reporting
included. How SQL Server Reporting compares with Crystal Reports?
Secondly, while generating reports from Windows OS on a Dot-Matrix
Printer, printing is slow. How can I print at a speed same as in DOS?Hi
create table #t (id int not null primary key,
date datetime,empid int,code char(2),
amount int)
insert into #t values (1,'20070701',1,'A1',100)
insert into #t values (2,'20070701',1,'B1',100)
insert into #t values (3,'20070701',2,'A1',150)
insert into #t values (4,'20070701',3,'B1',50)
insert into #t values (5,'20070701',4,'C1',120)
insert into #t values (6,'20070701',4,'D1',80)
select empid, max([A1])A1, max([B1])B1, max([C1])C1, max([D1])D1 from #t as
t1
pivot
(
max(amount)
for code IN([A1], [B1], [C1], [D1])
) AS pvt
group by empid
"RP" <rpk.general@.gmail.com> wrote in message
news:1188283745.388192.128690@.i13g2000prf.googlegroups.com...
>I am using SQL Server 2005 Express Edition.
> I have a table whose structure is follows:
> Date
> EmpID
> Code
> Amount
> Sample data looks like this:
> ---
> Date EMPID Code Amount
> ---
> 1-Jul-07 1 A1 100
> 1-Jul-07 1 B1 100
> 1-Jul-07 2 A1 150
> 1-Jul-07 3 B1 50
> 1-Jul-07 4 C1 120
> 1-Jul-07 4 D1 80
> ----
> The Codes are not fixed, they can be of any number and varies from
> employee to employee.
> I want to show a report like this:
>
> Month: Jul-07 -- CODES --
> ---
> EmpID A1 B1 C1 D1
> ---
> 1 100 100 0 0
> 2 150 0 0 0
> 3 0 50 0 0
> 4 0 0 120 80
> ----
> Since Codes are not fixed, so an SQL query is difficult. What is the
> alternative approach?
> I am thinking to create another table at run-time that reads column
> values (Code names) from the original table and create a field of that
> name in the next table.
> I also want to know in which edition of SQL Server is reporting
> included. How SQL Server Reporting compares with Crystal Reports?
> Secondly, while generating reports from Windows OS on a Dot-Matrix
> Printer, printing is slow. How can I print at a speed same as in DOS?
>