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 please
0
wmca
11/1/2005 1:40:55 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

3 Replies
774 Views

Similar Articles

[PageSpeed] 25
Get it on Google Play
Get it on Apple App Store

This 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.

0
PDraigh
11/1/2005 2:39:40 PM
Along the lines of what PDraigh has advised, you might find this post helpful: http://forums.asp.net/1041295/showpost.aspx

Terri Morton
Engagement Manager, Neudesic

How to ask a question

0
tmorton
11/1/2005 4:37:10 PM
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.
0
wmca
11/11/2005 11:27:22 AM
Reply:

Similar Artilces:

Can sql queries be nested in sql queries?
I am having problems with a template that uses the information from one sql query to generate a query on another table. Let me be more specific. The template in question receives a category ID value from a web page that provides a menu of several categories to choose from. Each Category ID represents several Items or products to choose from. When the template receives the category ID, the first thing it does is run a query on my item_category table (this table lists all of the items and associates them with the appropriate category). This query returns several itemIDs that are all...

sql or my sql query
hi,in my database  i have the two tables...1)programs                                2)reservedin the programs...two fields 1)program id           2)maxseats................these details wil be entered by adminin the reserved table  two fileds ..1)PRogramid        2)statusmy requirement is .. i need the available seats..meansif any body is intersted in  any program...they wil select the program...then status becomes approved(1)..then i need the o/p as ...

SQL Query , SQL or Procedural
HIHi i need some help on what the SQL query would be, let's say i have a resultant table like this. Group TestID Result A AA 10 A BB 30 A CC 20 B AB 200 B AC 300 A DD 50 C AA 1000 and i need to query by group and re-name every field as its TestID value for that selected group:  Example:if group "A" is selected: AA BB CC DD 10 30 20 50 if group "B" is selected: AB BC 200 300 and so on.. thanks.   Here is a sample for you to play with PIVOT: SELECT [...

SQL query that works in SQL Server Management Studio, but doesn't on .NET 2.0 page
SELECT favorites.FID, favorites.filename, favorites.username, files.status, files.private, files.views, files.title FROM favorites INNER JOIN files ON favorites.filename = files.filename WHERE (favorites.username = @username) AND (files.status IS NULL) AND (files.private = @private)@private is manually set to 'no'@username is set to profile.usernamewhen I run the above query in microsoft sql server mgmt studio express on the database, it presents all the information i'm asking for very easily. however when i try to implement this query on a aspx .net2.0 page with teh sqldatasourc...

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

Huge SQL Queries in SQL 2005
Good evening:  We're porting an old app written in ASP.NET  1.1433 and SQL 2000 to ASP.NET 2.0 and SQL 2005.  In the old app we have a few data grids that are populated from a dataset pulled from the database.  We use a SQL query that we build based on more than 10 different user inputs, the result of which is an enormously complicated SQL string.  We'd like to move this processing into a SPROC in the 2005 database. Rather than writing stored procedures to create the SQL SELECT statement, is it possible to pass an entire select state ment to a SPROC and hav...

C# Ado.net Sql query parameter query
Hi All, I'm trying to pass in a parameter value from an array in a loop that is used in a sql query and the results are populated to an xml file. The trouble is that I'm only getting the colums values in the outputted xml file. So I feel that the paramter is not being read. So can anyone help as I'm really stuck on this one. The code is as follows : public void DisplayUserInfo() { ArrayList UserIdArrayList = IdentifyUserID(); foreach(string ShowUserIDString in UserIdArrayList) { try { SqlConnection SqlConn = new SqlConnection(DBConnString)...

Datasource Sql query
 Hi, I am a coldfusion developer, but have now moved over to ASP.NET using VS2008 Pro, so trying to learn it quickly. My problem is that I have created a datasource with a simple query and when I run it in the query builder it works fine, however when I run it on the page I get nothing. Here is my query    <asp:SqlDataSource ID="qAllTrucks" runat="server"         ConnectionString="<%$ ConnectionStrings:ConnectionString %>"         ProviderName="<%$ Con...

Nested SQL Queries
Hey guys, I'm new to DB programming in .net and am having trouble with trying to display an catalouge on a webpage.I have created the following table: category{id, title, level, parent} id - identifiertitle - text valuelevel - the level of the catalouge entryparent - the parent of this categoryIn classic asp i would have neested a query e.g. loop all db entries where level = 0      current = rs(id).value      loop all entries where parent = current            displ...

Nest SQL queries
Hi,I have the followind code, which selects product categories and writes them to a <div> then for each product category, it also gets the associated products and writes them to a list inside the relevant div, so my output would be something like: ProductCategory number 1First product in category 1Second product in category 1Third product in category 1Product Category number 2First product in category 2Second product in category 2etc etcThe code is like this:        SqlCommand comm_productcategory = new SqlCommand("SELECT productcategory_id,...

SQL
Apologies for asking such a simple question, but here goes. I have two tables, for the sake of argument call them a and b . create table a (id numeric, checkdate datetime) create table b (id numeric, a_id numeric, checkdate datetime) I want to update the a table with the most recent checkdate of any related b record. In Oracle, this would be update a ta set checkdate = (select max(checkdate) from b tb where tb.a_id = ta.id) When I try to run this in Sybase, I get: The column prefix 'ta' does not match with a table name or alias name used in the query. Either th...

Nesting a looping query withing a looping query
Im having a issue. Im not sure how I am going to carry out but I have two tables in SQL server 2005 TABLES      Category                    SubCategory           (PK)CategoryName      (PK) SubCategoryNameCategoryID                    SubCategoryIDDate        &...

how to connect sql server using sql query?
Hi all, Please explain how to connect sql server using sql query?. My problem is need to get the data from one server table and insert into another server tables using stored procedure. It is possible by using C# coding. But i need to do using stored procedure. becoz each server have own connection string, username and password.  Please anyone help me? Thanks in advance!!! Hi, You can create a linked server between the two databases and then fire the queries. http://www.sqlservercurry.com/2008/03/how-to-set-up-your-database-for.html   HTH,Suprotim AgarwalA...

SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)?  Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...

Web resources about - Nested Loop in SQL Query - asp.net.sql-datasource

Nested stack automaton - Wikipedia, the free encyclopedia
Like a stack automaton , a nested stack automaton may step up or down in the stack, and read the current symbol; in addition, it may at any place ...

Indie Impressions - Nested - YouTube
A really strange and kinda amazing little experiment/experience by developer Orteil. A whole universe stacked up in a text based hierarchy for ...

How to create nested folders in iOS 8
... 8 (because there are a lot of them). Nesting folders on iOS 8 is easy to do and completely reversible… At this moment, we’ve test the nested ...

This iOS 7 Beta Glitch Lets You Create Functional Nested Folders
... of Newsstand with iOS 7 beta . Now, another glitch has been discovered by VBarraquito on Twitter that lets you create fully functional nested ...

Windows 10 Insider Preview Build 10565 introduces support for nested virtualization
... its Windows-as-a-service strategy, employing the help of Windows Insiders to test all the major changes. One such change is support for nested ...

Shakshuka: Eggs Nested in Spicy Tomato Sauce
... sauce that adds a punch of taste along with its burn. It's a fiery way to wake up! Genie] A recipe for Shakshuka (shakshouka), or eggs nested ...

Gmail Adds Nested Labels and Message Preview
... Folders4Gmail and Mihai Parparita's Conversation Preview , two scripts with similar functionality to the new Gmail Labs experiments. Nested ...

Recapitulation in Nested Evolutionary Dynamics
... …The cosmos is "selfishly" focused upon the overarching objective of achieving its own replication.” Gardner concludes with another nested spiral ...

nested baker set
... want to serve it in, which for me tends to be something simple and ceramic. Judy Jackson is one of my favorite ceramicists, and these new nested ...


Resources last updated: 12/10/2015 11:32:39 AM