Nested Loop in SQL Query

Hi,
I'm probably missing something obvious (either that or doing this totally wrong).
I'm trying to use a nested loop to generate the following results:
Unit Day1 Day2 Day3 Day4 Day5
Name1 25 45 89 54 76
Name2 48 54 81 74 98
What I have so far is this:
WHILE @.FCount < @.TotalFoodUnits
BEGIN
SELECT
(SELECT Unit FROM tbl_acc_FoodVenues WHERE UnitID = (@.FCount + 1)) AS Unit

WHILE @.FDCount < @.Days
BEGIN
SELECT
(SELECT FdRevenue_a FROM tbl_acc_aud_SportsAudits WHERE AudDate = DATEADD(day, @.FDCount, @.pdStartDate)) AS Rev
SET @.FDCount = @.FDCount + 1
END
SET @.FCount = @.FCount + 1
END
Any suggestions pleaseThis won't do what you want. It looks like you need to do a pivot-table type query that aggregates your data. With loops, you're just going to generate a whole bunch of separate result sets. Doing a pivot type table is difficult if you don't know how many columns you'll have. One thing I've done is create a dynamic SQL statement with cursors that pivots them all together. When the statement is built, it looks like it was basically hardcoded, but it is correct for the data as it is when it is run. The cursor(s) you need depend on what the varying numbers of rows or columns are that you have. Build your SQL statement by looping through the cursors as needed. See if you can hardcode a statement that will work and then look to see what the repeating elements are and how would you get those repeating elements with a SQL statement. You may need to query schema tables to get field names or use DISTINCT to get what values you have. I know thi sisn't very clear, but if you hardcode it and then see how that statement repeats, you might see what I'm talking about.
|||Along the lines of what PDraigh has advised, you might find this post helpful:http://forums.asp.net/1041295/showpost.aspx
|||Hi,
Thanks for the info above, it actually led me to this page:
http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
This provides an alternative solution which pivots the data using .net rather than trying to force sql into doing it for you.