Sql server pivot table or inner join for daterange and sum column

Question

I have a sql problem and not sure how to go about it because I'm not expert in SQL.

My outcome is:

**MONTH | 2011 | 2012**
1   8894108.372544  9200915.88732
2   8987154.877656  8188366.52079
3   8135004.323592  9383008.68889201
4   8374239.052416  9660272.13007201
5   9525066.469164  8578163.904876
6   7475397.368328  8606525.720634
7   8992114.52414401    9365367.617496
8   9358753.61943   11048712.924366
9   8853398.95447799    8499947.810022
10  8577323.223498  9225470.14965
11  9265685.67622799    9226157.80527
12  8887908.50775001    8318114.32842

Here is my sql.

SELECT MONTH( ts.TransactionDate) as [TransactionDate], 
ISNULL(SUM(CAST([SalePrice] AS FLOAT)), 0) AS "SUM([SalePrice]) 2011", s.[SUM([SalePrice]]) 2012]
FROM  [TABLESALES] ts
INNER JOIN (SELECT MONTH( t.TransactionDate)  as [TransactionDate], 
ISNULL(SUM(CAST([SalePrice] AS FLOAT)), 0) AS "SUM([SalePrice]) 2012" 
FROM  [TABLESALES]  t
WHERE [TransactionDate] BETWEEN '2012-01-01' AND '2012-12-31' GROUP BY MONTH( t.TransactionDate)) s on s.TransactionDate = MONTH( ts.TransactionDate)
WHERE ts.TransactionDate BETWEEN '2011-01-01' AND '2011-12-31'
GROUP BY MONTH(ts.TransactionDate), s.[SUM([SalePrice]]) 2012]
ORDER BY [TransactionDate]

The Problem I have is the date range could be "2011" to "2015". So I dont want to create inner joins multiple times to do this. Is there a better way and please can I have some code examples. Thanks in advance.

I know I am doing a single join when there is date range of 1 year. "2011 to 2012". But its not great when I have to do 2011-2015 so thats 4 years which means 4 inner joins?

You are looking for a dynamic pivot like this http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — A Hocevar, Aug 05 '15 at 12:35

score 0 · Answer 1 · answered Aug 05 '15 at 12:41

This query will pivot the data:

; With Sales(Y, M, sale) as(
    Select Year(TransactionDate), Month(TransactionDate), SalePrice From TABLESALES
    Where TransactionDate >= '20120101' and TransactionDate< '20150101'
)
Select M, [2012], [2013], [2014]
From Sales
Pivot(
    Sum(Sale)
    For Y In ([2012], [2013], [2014])
) as Piv

You only need to update the where clause (start and end years) and add all years in the select and for

Sql server pivot table or inner join for daterange and sum column

1 Answers1