包含每个月列的帐户数据的 PIVOT 表 [英] PIVOT table for account data with columns for each month

问题描述

我正在查看的表格包含以下列:

The table I am looking in has the following columns:

department_number, amount, date_created.

我正在尝试创建一个表格，显示 2013 年每个月每个部门的总金额，所以它看起来像这样:

I'm trying to create a table that shows the total amount for each department for each month in 2013, so it would look like this:

Department_number, Jan Amount, Feb Amount, March Amt, etc....

这是数据透视表的情况，还是我应该每个月运行不同的查询并加入它们?

Is this a case for pivot tables, or should I just run a different query for each month and join them?

推荐答案

您的案例当然适合使用 PIVOT 表语法.下面是一个简单的查询，它确实枢轴.

Your case is certainly a candidate for using PIVOT table syntax. The below is a simple query which does pivot.

SELECT Department_number
,[January]
,[February]
,[March]
FROM (
SELECT Department_number, Amount, datename(date_created) AS month_created from <Your_Table>
) AS SOURCETABLE
PIVOT(SUM([Amount]) FOR month_created IN ([January],[February],[March])) AS PIVOTTABLE

此查询假设您的表中有包含值 Jan、Feb、March 的 date_created 列.如果需要，您可以增加更多的月份.

This query assumes you have date_created column containing values Jan,Feb,March in your table. You may add more months if you need.

有关该主题的更多信息 - http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

More on the subject - http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

这篇关于包含每个月列的帐户数据的 PIVOT 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！