SQL Pivot question

Question

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!

Answer 1

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

Answer 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.