By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,584 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Loop in SQL

Uncle Dickie
P: 67
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
Nov 4 '08 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
Will this help?

-- CK
Nov 4 '08 #2

Uncle Dickie
P: 67
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.
Nov 4 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.