I'm having a hard time getting my head around a query im trying to build with SQL Server 2005.
I have a table, lets call its sales:
SaleId (int) (pk) EmployeeId (int) SaleDate(datetime)
I want to produce a report listing the total number of sales by an employee for each day in a given data range.
So, for example I want the see all sales in December 1st 2009 - December 31st 2009 with an output like:
EmployeeId Dec1 Dec2 Dec3 Dec4
1 10 10 1 20
2 25 10 2 2
..etc however the dates need to be flexible.
I've messed around with using pivot but cant quite seem to get it, any ideas welcome!
Here's a complete example. You can change the date range to fit your needs.
use sandbox;
create table sales (SaleId int primary key, EmployeeId int, SaleAmt float, SaleDate date);
insert into sales values (1,1,10,'2009-12-1');
insert into sales values (2,1,10,'2009-12-2');
insert into sales values (3,1,1,'2009-12-3');
insert into sales values (4,1,20,'2009-12-4');
insert into sales values (5,2,25,'2009-12-1');
insert into sales values (6,2,10,'2009-12-2');
insert into sales values (7,2,2,'2009-12-3');
insert into sales values (8,2,2,'2009-12-4');
SELECT * FROM
(SELECT EmployeeID, DATEPART(d, SaleDate) SaleDay, SaleAmt
FROM sales
WHERE SaleDate between '20091201' and '20091204'
) src
PIVOT (SUM(SaleAmt) FOR SaleDay
IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])) AS pvt;
Results (actually 31 columns (for all possible month days) will be listed, but I'm just showing first 4):
EmployeeID 1 2 3 4
1 10 10 1 20
2 25 10 2 2
I tinkered a bit, and I think this is how you can do it with PIVOT:
select employeeid
, [2009/12/01] as Dec1
, [2009/12/02] as Dec2
, [2009/12/03] as Dec3
, [2009/12/04] as Dec4
from sales pivot (
count(saleid)
for saledate
in ([2009/12/01],[2009/12/02],[2009/12/03],[2009/12/04])
) as pvt
(this is my table:
CREATE TABLE [dbo].[sales](
[saleid] [int] NULL,
[employeeid] [int] NULL,
[saledate] [date] NULL
data is: 10 rows for '2009/12/01' for emp1, 25 rows for '2009/12/01' for emp2, 10 rows for '2009/12/02' for emp1, etc.)
Now, i must say, this is the first time I used PIVOT and perhaps I am not grasping it, but this seems pretty useless to me. I mean, what good is it to have a crosstab if you cannot do anything to specify the columns dynamically?
EDIT: ok- dcp's answer does it. The trick is, you don't have to explicitly name the columns in the SELECT list, * will actually correctly expand to a column for the first 'unpivoted' column, and a dynamically generated column for each value that appears in the FOR..IN clause in the PIVOT construct.
