467,915 Members | 1,221 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,915 developers. It's quick & easy.

Loop in SQL

Uncle Dickie

I have a bit of code that creates a 'cross-tab' type result set from table data as below:

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
sum(case YearSales when @yrmin then TotalSales else 0 end) as @yrmin
@yrmin = @yrmin+1
FROM #temp1
GROUP BY PartNumber

Any help would be greatly appreciated!

Nov 4 '08 #1
  • viewed: 1280
2 Replies
Expert 2GB
Will this help?

-- CK
Nov 4 '08 #2
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.
Nov 4 '08 #3

Post your reply

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

Similar topics

reply views Thread by Charles Alexander | last post: by
43 posts views Thread by Gremlin | last post: by
5 posts views Thread by Martin Schou | last post: by
32 posts views Thread by Toby Newman | last post: by
2 posts views Thread by Alex | last post: by
3 posts views Thread by Ben R. | last post: by
32 posts views Thread by cj | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.