SQL Pivot问题 [英] SQL Pivot question

问题描述

在尝试使用SQL Server 2005构建即时查询时，我遇到了困难.

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)

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.

例如，我希望查看2009年12月1日至2009年12月31日的所有销售，并显示以下输出:

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但是日期必须灵活.

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

结果(实际上将列出31列(适用于所有可能的月份)，但我只显示前4列):

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

这篇关于SQL Pivot问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！