CODEWITHSUNDEEP
sqlt-sqlsql-server-2005
Kerberos42
Kerberos42

Reputation: 233

SQL Server 2005 pivot query syntax assistance

I've read up on pivot in SQL and looked at tons of examples, but I just can't get this to work the way I'd like. I have a transaction history table that tracks employee purchases.

Example:

Employee | Date       | Value
1234     | 2012-11-13 | 20.23

I need to create a summary for all employees with total sales for each month like this:

Employee | Jan | Feb | Mar | ... | Dec| YearTotal

I'm confused about grouping and getting the monthly totals. Thanks for any help!

EDIT Okay, I apologize if I came across as unprepared to request assistance. I've been working on this most of the day, and was getting frustrated and tired. I've got over 10 years of experience with SQL, however this is the first time I've tried to do a pivot query and I'm just not getting my head around how they are supposed to work, and how to make it work for my needs. I need to produce these numbers for Monday morning, so in about 7 hours. My last ditch effort will be to run a seperate query for each month, then dump the data into excel for the report. I'd like this to be seamless for the end user to run in the future.

Here's a simplified version of my attempt that was reworked from a sample from MSDN:

SELECT CUSTOMER_NUMBER, [jan], [feb]
FROM 
    (
    SELECT month(transaction_date)
    FROM customer_item_purchases
    WHERE TRANSACTION_DATE BETWEEN '2011-11-16' AND '2012-11-15'
    )
PIVOT
(SUM(SALES_VALUE) IN ([JAN], [Feb]))
AS pvt

I need total monthly sales for each month between the dates specified. Nov will show up at the beginning and end of the month columns. The source table is simple: Employee, transdate, receipt, location, etc.

Upvotes: 2

Views: 721

Answers (2)

AaronLS
AaronLS

Reputation: 38364

Fiddle example(not sure if this will save if I'm not logged in but works now: http://sqlfiddle.com/#!6/8c96a/3/15

I only pivoted 3 months, but you get the idea hopefully. Usually you have the nested Select after From, because you only want to pivot/group on certain columns. Then the Pivot occurs, where you list the values that you want to become column names FOR mnth IN ( [1], [2], [3] ) which corresponds to the Month(Date) as mnth field.

Create table Sales
( EmpId int, Date DateTime, Value integer );

Insert Into Sales (EmpId, Date, Value)
Values (1,'1/1/2012', 12);
Insert Into Sales (EmpId, Date, Value)
Values (1,'2/1/2012', 12);
Insert Into Sales (EmpId, Date, Value)
Values (1,'3/1/2012', 12);
Insert Into Sales (EmpId, Date, Value)
Values (1,'4/1/2012', 12);
Insert Into Sales (EmpId, Date, Value)
Values (1,'5/1/2012', 12);
Insert Into Sales (EmpId, Date, Value)
Values (1,'6/1/2012', 12);
Insert Into Sales (EmpId, Date, Value)
Values (1,'7/1/2012', 12);

Insert Into Sales (EmpId, Date, Value)
Values (2,'1/1/2012', 112);
Insert Into Sales (EmpId, Date, Value)
Values (2,'1/15/2012', 112);
Insert Into Sales (EmpId, Date, Value)
Values (2,'2/1/2012', 312);
Insert Into Sales (EmpId, Date, Value)
Values (2,'3/1/2012', 512);
Insert Into Sales (EmpId, Date, Value)
Values (2,'4/1/2012', 2);
Insert Into Sales (EmpId, Date, Value)
Values (2,'5/1/2012', 3);
Insert Into Sales (EmpId, Date, Value)
Values (2,'6/1/2012', 1);
Insert Into Sales (EmpId, Date, Value)
Values (2,'7/1/2012', 0);


SELECT EmpID, [1], [2], [3]
FROM 
(SELECT EmpID, Month(Date) as mnth, Value
FROM Sales) p
PIVOT
(
  Sum (Value)
  FOR mnth IN
( [1], [2], [3] )
) AS pvt
ORDER BY pvt.EmpID;

Upvotes: 3

mrkb80
mrkb80

Reputation: 591

You could setup a table with 2 columns one for each month (1,2,3,...,12) and the other the 3 digit month code ('Jan', 'Feb', 'Mar',...,'Dec'). You could also use a series of case statements wrapped in sums to only count certain month sales for each employee and then group by employee.

It is a matter of grouping and joining, two fundamental concepts in databases. I can just give you my answer, but if you really want to learn, then I would agree with Mitch...show some effort.

Upvotes: 0

Related Questions