Hello!
I have a bit of code that creates a 'cross-tab' type result set from table data as below:
#temp1:
PartNumber, YearSales, TotalSales
PartA, 2006, 1050
PartA, 2007, 8254
PartB, 2007, 1524
PartB, 2008, 1685
'Cross-Tab' query:
SELECT PartNumber
,sum(case YearSales when 2006 then TotalSales else 0 end) as [2006]
,sum(case YearSales when 2007 then TotalSales else 0 end) as [2007]
,sum(case YearSales when 2008 then TotalSales else 0 end) as [2008]
FROM #temp1
GROUP BY PartNumber
This works fine, but will obviously only show results in the defined year ranges in the case statements. I can add loads more lines in to the future, but is there a way of automatically expanding the case statement (or an equivalent)?
The kind of thing I mean is:
DECLARE @yrmin int, @yrmax int
SELECT @yrmax = (SELECT max(YearSales) FROM #temp1)
SELECT @yrmin = (SELECT min(YearSales) FROM #temp1)
SELECT PartNumber
,WHILE @yrmin <= @yrmax
BEGIN
sum(case YearSales when @yrmin then TotalSales else 0 end) as @yrmin
@yrmin = @yrmin+1
CONTINUE
END
FROM #temp1
GROUP BY PartNumber
Any help would be greatly appreciated!
Richard