I'm trying to find a better way of reporting on some of our data within SQL Server, currently we have a report that looks at sales value in a certain country pivoted by year.
The report give me the correct data, but every year i'm having to add an extra column in for the new year. I've read a little bit about Dynamic pivot queries, but I cannot make them to work.
Here is my current query:
SELECT
isnull([TYPE],'Total') as 'Type',
sum(isnull([2010],0)) as '2010',
sum(isnull([2011],0)) as '2011',
sum(isnull([2012],0)) as '2012',
sum(isnull([2013],0)) as '2013',
sum(isnull([2014],0)) as '2014',
sum(isnull([2015],0)) as '2015',
sum(isnull([2016],0)) as '2016',
sum(isnull([2017],0)) as '2017',
sum(isnull([2018],0)) as '2018',
sum(isnull([2010],0) +isnull([2011],0) +isnull([2012],0) +isnull([2013],0) +isnull([2014],0) +isnull([2015],0) +isnull([2016],0) +isnull([2017],0) +isnull([2018],0)) as 'total'
FROM
(
SELECT
T3.Name 'Type' ,
DATEPART(YEAR,T0.DocDate) 'Year',
SUM(T1.totalsumsy) 'Quantity'
FROM
OINV T0 with (nolock) INNER JOIN
INV1 T1 with (nolock) ON T0.DocEntry = T1.DocEntry left join
INV12 t9 with (nolock) on t0.DocEntry = t9.DocEntry inner join
OCRD t7 with (nolock) on t0.CardCode = t7.CardCode INNER JOIN
[@AA_REGION] T8 ON case when isnull(t0.u_b2c,'n') = 'y' then t9.countrys else ISNULL(T7.U_COUNTRY,T7.COUNTRY) end = T8.CODE AND T8.code = 'au' INNER JOIN
OITM T2 with (nolock) ON T1.ItemCode = T2.ItemCode LEFT JOIN
[@AA_ITEMTYPES] T3 ON T2.U_CATEGORY = T3.Code LEFT JOIN
[@AA_STYLE] T4 ON T2.U_Style = T4.Code LEFT JOIN
[@AA_STYLEGROUP] T5 ON T4.U_GROUP = T5.Code LEFT JOIN
[@AA_ANIMAL] T6 ON T2.U_ANIMAL = T6.Code
WHERE
T5.Code = '010' AND
T6.CODE = '001' and
T0.DocDate >= '01.01.2010'
GROUP BY
T3.Name ,
DATEPART(YEAR,T0.DocDate)
)PS
PIVOT
(SUM(Quantity) for [Year] in ([2010],[2011],[2012],[2013],[2014],[2015],[2016], [2017], [2018]) ) as pvt
group by
[type] with rollup
order by sum(isnull([2010],0) +isnull([2011],0) +isnull([2012],0) +isnull([2013],0) +isnull([2014],0)+isnull([2015],0)+isnull([2016],0)) desc
Can anyone point me in the right direction to make the above query dynamic by year?