PIVOT table for account data with columns for each month

Question

The table I am looking in has the following columns:

department_number, amount, date_created.

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?

Must it be done in sql, tools like excle can 'link' to the data and pivot this for you. — T I, Jan 16 '14 at 14:05

IndoKnight · Accepted Answer · 2014-01-16T15:25:43.033

2

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

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

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

edited Jan 16 '14 at 15:25

answered Jan 16 '14 at 14:30

IndoKnight

1,846
1
21
29

1

What if the date_created is a datetime data type? can I use month(date_created) ? – Beans Jan 16 '14 at 15:21
1

Thank you very much, I was able to get this to work. – Beans Jan 16 '14 at 15:58

score 0 · Answer 2 · edited May 23 '17 at 12:11

0

If you want to display this in SSRS report then go for Row and Column grouping and aggregate the amount otherwise you can use Pivot Table with TSQL : How to pivot table with T-SQL?

edited May 23 '17 at 12:11

Community

1
1

answered Jan 16 '14 at 14:05

Naveen Kumar

1,541
10
12

1

this is more of a comment then an answer. – T I Jan 16 '14 at 14:05

PIVOT table for account data with columns for each month

2 Answers2

Linked