Dynamic Pivot with overlapping years [duplicate]

Question

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

Answer 1

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