包含每个月列的帐户数据的 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屋!