# Dynamic Pivot with overlapping years [duplicate]

This question already has an answer here:

I have looked and cannot find how to show the last # months in a pivot table. Everything I seem to run across codes months statically. I'm seeking a solution that shows something similar to the below result:

The columns headers are a unique identifier for the month(year*12+monthnum). This was the only way I could think of for progressing year and month in a linear manner.

`````` Partnumber 24180   24179   24178   24177   24176

813453     0       2       4       6       3
``````

Is this possible? Am I thinking too hard? Each month the columns would naturally shift, and this is where I can't figure out how to dynamically do this.

Any suggestions or assistance is appreciated.

The table holding the data looks like this: Of course the actual table hold much more historical data for thousands of parts.

``````PN      Year    Month   Usage   UID
813453  2014    12      0       24180
813453  2014    11      2       24179
813453  2014    10      4       24178
813453  2014    9       6       24177
813453  2014    8       3       24176
``````

## marked as duplicate by Paul White♦Sep 27 '15 at 1:26

You can use `PIVOT` with dynamic sql to solve your problem. Below is a simple implementation to solve the issue. The query will support any number of months you may have. Make sure the length of `@pivcols` is large enough to accommodate the maximum anticipated string length for all the current and future months (povot columns) concatenated.

``````--Table structure--

Create table #T
(
PN int
,Year int
,Month int
,Usage int
,UID int
);

Insert into #T values(813453,  2014,    12,      0,       24180)
Insert into #T values(813453,  2014,    11,      2,       24179)
Insert into #T values(813453,  2014,    10,      4,       24178)
Insert into #T values(813453,  2014,    9,      6,       24177)
Insert into #T values(813453,  2014,    8,      3,       24176);

--Dynamic pivot--

declare @pivcols as varchar(800);

select  @pivcols = stuff((select '],[' + cast((Year*12 + Month) as varchar(10))
from #T order by Year*12 + Month desc
for xml path('')), 1, 2, '') + ']';

declare @pivquery varchar(500);

set @pivquery = 'Select PN, '+
@pivcols +'
from
(select PN, Usage,
cast((Year*12 + Month) as varchar(10)) rnk
from #T
) as d
PIVOT
(
max(Usage)
for rnk in ( '+ @pivcols +' )
) AS pvt ';

EXECUTE(@pivquery);

--Results--

PN      24180   24179   24178   24177   24176
813453  0       2       4       6       3
``````