Loop in SQL

[Uncle Dickie]

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

[ck9663]

Will this help?

-- CK

[Uncle Dickie]

Thanks, that looks like it will do the trick. I'm playing with it now and just need to change a few things more to get exactly what I'm after..

I did have to modify the function slightly as 'pivot' is a reserved word in SQL 2005, but other than that it works a treat.