How do I ... loop horizontal?

If I have the raw data dumped into a big table as following:

Date P R M E Date P R M E Date P R M E Date P R M E
1/1/90 1 2 3 4 1/1/90 2 3 4 5 1/1/90 3 4 5 6 1/1/90 4 5 6 7
...
1/1/05 1 2 3 4 1/1/05 2 3 4 5 1/1/05 3 4 5 6 1/1/05 4 5 6 7

And this table has a repeating block [D, P, R, M,E] 300 times. Is it possible to write a loop query/stored procedures/triggers (or whatever it is) to read each repeating block and stack them on top of each other to insert into another table which has the same structure as following?

Look like this?
Date P R M E
1/1/90 1 2 3 4
...
1/1/05 1 2 3 4
1/1/90 2 3 4 5
...
1/1/05 2 3 4 5
1/1/90 3 4 5 6
...
1/1/05 3 4 5 6

If there is a solution would you please elaborate, example?
Thank you for the help.
shiparsonsUsing Perl this would be relatively easy (basically a one line script).

Using pure SQL Server tools (BCP and Transact-SQL), it can be done, but it would be rather ugly.

I'd try to request the data in another format. It would be easier if it was provided in a "cleaner" format. If that isn't a choice, look at the tools that you've got to see what makes the most sense, then use that to fix the problem.

-PatP|||I know when I'm looped I'm usually horizontal :D|||you're not drunk if you can lie on the floor without holding on|||shiparsons, please do not post two threads with the same subject on the same forum. If you have additional information, just append it as a new post.

If the number of blocks is constant, you could do this as a single butt-ugly UNION statement.

Otherwise, it is dynamic SQL time for you.

I may have a dynamic SQL algorithm that would be pretty concise for you. I'll try it in the morning if nobody else posts it first...|||You can use a simple for loop in DOS/NT Shell language to parse the file and generate new output to a redirected file if you do not have access to perl or a tool such as MKS Toolkit

It may be a bit of of work since you have multiple rows on a single line.

Google "for loop" "DOS" and you should be able to get some assistance.|||Here is some code that selects N columns at a time from any table.

You can modify it to do inserts, if you would like.

declare @.ColumnString varchar(500)
declare @.ColumnCounter int
declare @.ColumnIncrement int
declare @.TableName varchar(500)
declare @.NumColumns int

set @.ColumnIncrement = 3 --Number of columns to return for each statement
set @.TableName = 'TableName' --Name of your target table

set @.ColumnCounter = 0
set @.NumColumns =
(select count(*)
from sysobjects
inner join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.name = @.TableName)

while @.ColumnCounter < @.NumColumns
begin
set @.ColumnString = null
set @.ColumnCounter = @.ColumnCounter + @.ColumnIncrement

select @.ColumnString = isnull(@.ColumnString + ', ', '') + syscolumns.name
from sysobjects
inner join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.name = @.TableName
and colid > @.ColumnCounter - @.ColumnIncrement
and colid <= @.ColumnCounter
order by colid

exec ('select ' + @.ColumnString + ' from ' + @.TableName)
end|||Thank you for the suggestion and assistance.
I am trying to avoid using other "Langauage/command" and use SQL instead. I belive there should be a way to achieve what I am trying to do within SQL. It may need multiple steps.

I appreciate any brainstorming and help.
shiparsons|||Did you try the code I posted?|||Blindman,
Thank you for the help. I guess while I was composing my respose you just posted the code. Anyway, I will try and definitely let you know.

Many thanks,
shiparsons|||Blindman,
I have tried the code... You are the best! It works exactly what I want!

I tried to modify the code to insert into a table but got error message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test1Date'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test2Date'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test3Date'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test4Date'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test5Date'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test6Date'.
....

Here is the code I modified. Any suggestion? Thank you in advance for the help!!
declare @.ColumnString varchar(500)
declare @.ColumnCounter int
declare @.ColumnIncrement int
declare @.TableName varchar(500)
declare @.NumColumns int
declare @.Staging varchar(500)

set @.ColumnIncrement = 8
set @.TableName = 'TestTable'
set @.Staging= 'Staging'
set @.ColumnCounter = 0
set @.NumColumns =
(select count(*)
from sysobjects
inner join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.name = @.TableName)

while @.ColumnCounter < @.NumColumns
begin
set @.ColumnString = null
set @.ColumnCounter = @.ColumnCounter + @.ColumnIncrement

select @.ColumnString = isnull(@.ColumnString + ', ', '') + syscolumns.name
from sysobjects
inner join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.name = @.TableName
and colid > @.ColumnCounter - @.ColumnIncrement
and colid <= @.ColumnCounter
order by colid

exec ('insert into' + @.staging + 'select ' + @.ColumnString + ' from ' + @.TableName)

end|||Why are you inserting into your staging table? I though the idea was to transform data while populating production tables?

I think it is erroring out because your dynamic INSERT statement does not list which column(s) to insert into.

Copy this just before your EXEC statement and see what code it is trying to run:

'insert into' + @.staging + 'select ' + @.ColumnString + ' from ' + @.TableName

If your Query Analyzer is set to output Text results then you can just copy the output into another QA window and execute it directly to trace your error.

Error tracing is one of the challenges of dynamic SQL.|||I still need to do some manipulations before load into production table. Thats why I need to insert into staging table.

I tried the code right before EXCE statement and still got error message:

Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'insert into'.

Any idea?

Thanks|||Comment out the EXEC statement so that you don't throw the error anymore, and instead post the dynamic SQL statement that would be executed.|||I replaced Exec (..) with the command :

'insert into' + @.staging + 'select ' + @.ColumnString + ' from ' + @.TableName

And run the entire code. However, I am still getting the error:
Server: Msg 170, Level 15, State 1, Line 32
Line 32: Incorrect syntax near 'insert into'.

What part is incorrect? I could not figure it out.

Thanks for the help again.
shiparsons|||how 'bout:

Declare @.sql varchar(8000), @.staging sysname, @.ColumnString varchar(12), @.TableName sysname, @.debug bit

Select @.staging = '@.t1', @.TableName = '@.t2', @.ColumnString = 'c2', @.debug = 0

Select @.sql = 'Set NoCount On Declare @.t1 table(c1 int) Declare @.t2 table(c2 int)'
Select @.sql = @.sql + char(10) + 'Insert @.t2 (c2) Select 1 Union Select 2'

Select @.sql = @.sql + char(10) + 'insert ' + @.staging + ' select ' + @.ColumnString + ' from ' + @.TableName
Select @.sql = @.sql + char(10) + 'Select * From @.t1; Select * From @.t2'

If @.debug = 1 Select @.sql
Else Exec (@.sql)|||Blindman,
I think I know the problem is...
The entire code you posted which pulls out the results I want and in addition it stacks all the column headings as well. That's why when I insert into 'staging table' error msg shows up.

Here is the example of what I mean:
the results from running the code

date column1 column2... column8
data set...
date column10 column11...column16
data set...
...

I have defined datatype for each column of my staging table as (date datetime and all else is float). That is why 'insert into' caused error. Is there any a way to modify or skip the headers?

Any suggestions are welcome!
Thanks for the help!
shiparsons|||I hope you meant that you replaced it with

SELECT 'insert into' + @.staging + 'select ' + @.ColumnString + ' from ' + @.TableName

It should not give you an error...

...and yes, it is likely that the problem is a lack of specifed columns for your insert statement. If the column names are the same, you should be able to run something like this:

exec ('insert into' + @.staging + '(' + @.ColumnString + ') select ' + @.ColumnString + ' from ' + @.TableName)|||Blindman,
I think the code should be:
exec ('insert' + 'into' + @.staging + '(' + @.ColumnString + ') select ' + @.ColumnString + ' from ' + @.TableName)

And I am still getting the error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test1Date'.
...
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test32Date'.

Unfortunately, all columns names are different ( total 32 which is standard for each input file).

Any other ideas?
Thanks.

MaxA,
I like your code which is simpler, however, like the problem i have with the code Blindman posted. Your code will mix the column headings into the results as well. I need to insert the results into staging table which has each datatype defined as either datetime or float. Is there a way not pulling the column headers as a part of results?

Results from your code
c1
----
1
2

c2
----
1
2

expected results so that I can insert into staging table
c1
----
1
2

1
2

Any help is highly appreciated!
shiparsons|||"exec ('insert' + 'into' + @.staging + '(' + @.ColumnString + ') select ' + @.ColumnString + ' from ' + @.TableName)"?

No, there isn't any need to split 'insert into' into 'insert' + 'into', which won't work anyway because you ommited the space character, and so it concatenates to "insertinto". That is certain to throw a syntax error.

To get around the different column names, try creating a view(s) based upon either your staging table or your production table that aliases the column names so they are compatible. Then reference the view in your statements rather than the table itself.

But it you are trying to insert into your staging table and your staging table is the same poorly designed schema you mentioned in your first few posts, then I think you are screwed because you are going to have a tough time getting your INSERT statement to insert into columns 1-5 on one run, 6-10 on then next, and so on...|||The results you are seeking are unexpected.

Remove ; Select * From @.t2 from my example - it was put there to demonstrate the accuracy of the Select * From @.t1.|||Blindman,
Here is the right code to insert into a table, which works exactly what I want!

insert into tablename
exec ('select ' + @.ColumnString + ' from ' + @.TableName)

Many thanks to everyone who replied this thread!

shiparsons

How could I return the next row(Or 8th row for example)

Hi,
I want to return the next row in a select ... order by ... cursor.
I don't want to loop,just SQL,Do you know any solution?
For example: in Oracle we use rownum,is there any equivalent in SQL Server?
-ThanksWhy don't you explain why you think you need a cursor? In Oracle cursors are required, but with SQL Server they are considered inefficent and are rarely used.|||Thanks blindman ,
Typical scenario may be looks like this: I return the result of a heavy select(think about many joins and ...) to the client application: I want just one row at this stage. The application processes that row and then it needs the next row in that select to process.How should I say: "The next row in that select"?
One solution: Use a table (maybe temporary table) to hold the result of that select with an identity column as the rownum of each row.
Another solution: Hold the result of select in application layer and go through a loop in application to fetch the next row.
I am just curious about how one could do this in an "on the fly" manner? You are right maybe it is not efficient in SQL Server but is there any way at all?
-Regards|||Well, I think you should hold the data in the application layer if you want to ensure a static dataset for processing. Problem is, if you go back to SQL Server for your "next" record, the underlying data may have changed. Is your application modifying the data and sending it back to SQL server to be updated?|||Is your application modifying the data and sending it back to SQL server to be updated?
No,it is not.So there is no phantom read problem.I think this is so inefficient to pull all data to client-side only because you may want the next row.Are you in agreement? SQL Server should think about this carefuly,IMHO!
-Thanks|||Well you may think that, but it doesn't, not in SQL 2K.

2k5 will have rownumber if I'm not mistaken...

But this is the best server side paging articles I've seen

http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx

And

http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx|||No,it is not.So there is no phantom read problem.I think this is so inefficient to pull all data to client-side only because you may want the next row.Are you in agreement? SQL Server should think about this carefuly,IMHO!
-Thanks
No, I'm not in agreement. How the data is displayed is up to the presentation layer and/or middle tier, and is not the database's responsibility. If the application demands paging, then the database needs to be DESIGNED to facilitate paging. Even ROWNUMBER is meaningless for repeated calls on dynamic datasets.|||How the data is displayed is up to the presentation layer and/or middle tier, and is not the database's responsibility.
OK,I am in agreement.Maybe this is about "Presentation of Data" but we know there is not a clear line between those layers: Many things can be done in Database layer but is implemented in "Application server or middle layer" and vice versa.
Thanks to your posts but I still think it is inefficient to pull whole data between layers and I became so happy when I heard that Yukon will have rownum from Brett.Thanks!|||I'd say use the system the way it was designed. Most of your time is not going to be pulling back an entire dataset, but cycling through it row by row. Databases are designed to work on results sets (i.e., not row by row) and thus aren't very efficient when you want to do that. However, procedural languages are designed to do that very thing. By way of example, I had a cursor running on the database when I first began posting here that took approximately 15 minutes to run. Through the help of those more knowledgable here, I was able to remove the cursor and get the same results in under 30 seconds. I guess the best way to test it is do it both ways, but I'm willing to bet you'll find that pulling the entire result set back and cycling in the app will be faster than coding a cursor in the database. Just my thoughts.

How can you stop and cycle through a column(s) of data?

Ok, at this point I have the reader reading the tables data in a loop while it's not empty. During the gathering of each row of data, I was wondering if it was possible to do a next row once I've reached a certain column. The main users table has just the one user, but it's relationship table has a couple family members. I was hoping someone could show me how to make it so that the one user and all his related family members will print out to a label.
while (reader.Read())
{
string usr = reader["UserName"].ToString();
usr = usr.TrimEnd();
string pss = reader["Password"].ToString();
pss = pss.TrimEnd();

if (usrNmeLbl.Text == usr)
{
if (psswrdLbl.Text == pss)
{
//read the column from the reader and cast it to String as some may contain null values
usrNmeLbl.Text = reader["FirstName"].ToString() + " ";
psswrdLbl.Text = reader["LastName"].ToString() + "<br />";
psswrdLbl.Text += "Place of Birth: " + reader["BirthPlace"].ToString() + "<br />";
psswrdLbl.Text += "<img src=" + reader["Photo"].ToString() + " />" + "<br />";
Label4.Text = "Your relatives: " + "<br />";
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
}
If I grab the Relation table data again, it's not cycled to the next relative. I was hoping that it would, but it's not. So I'm wondering if there was something that could be added to the second set.
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();

Thank you in advance.I tried this:

while (reader.Read())
{
string usr = reader["UserName"].ToString();
usr = usr.TrimEnd();
string pss = reader["Password"].ToString();
pss = pss.TrimEnd();
//take the read data from the reader and display it for testing purposes
if (usrNmeLbl.Text == usr)
{
if (psswrdLbl.Text == pss)
{
//read the column from the reader and cast it to String as some may contain null values
usrNmeLbl.Text = reader["FirstName"].ToString() + " ";
psswrdLbl.Text = reader["LastName"].ToString() + "<br />";
psswrdLbl.Text += "Place of Birth: " + reader["BirthPlace"].ToString() + "<br />";
psswrdLbl.Text += "<img src=" + reader["Photo"].ToString() + " />" + "<br />";
Label4.Text = "Your relatives: " + "<br />";
while (reader.Read())
{
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString() + "<br />";
}
}

But for some reason the relationships print out multiple times rather than just until there are no more relatives in the database.|||You will most likely get a usable response by posting this question in one of the .NET forums.|||

Arnie Rowland wrote:

You will most likely get a usable response by posting this question in one of the .NET forums.

Ok, this is where I am really confused. Why does the reading of data loop all over again from the beginning? I would think that when it read the last row that then it would just exit out of the loop. Just like what's happening with the first loop with while(reader.Read()).

while (reader.Read())
{
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString() + "<br />";
}

Ok, thank you. I will try.

How can i use a for loop in a query ?

hi all i have the following stored procedure.

declare @.AreaID int

select @.AreaID = 1

select DATEPART(hh, dbo.JobEvent.JobEventDateTime) AS hourbracket, count(ID) as NUMCOUNT

from
table1 INNER JOIN
table2 ON table1.JobId = table2.JobEvent_JobId
where

(table1.Job_AreaId = @.AREAID)

the there are about 300 AREAID's so my question is do i do a "LOOP" or is that no efficient and chews up resources on the server ?
what would be a nice clean way to do this ?

thanks
robby

Are you asking how to iterate over the results returned from your stored procedure?

depending on how you retrieve you results either from a data reader or you used a data adapter and filled it to a dataset

//if filled to a dataset you can do thisif (MyDataSet.Tables.Count != 0){foreach (DataRow rowin MyDataSet.Tables[0].Rows) {//now you can access each row returned }}//if using a data readerwhile (DataReader.Read()){//hear you can access each row }

|||

Hi,

if i lets say hardcode the areaid to lets say 1 i get a set of results. i want to next get the results for areaid 2 so as if i want to runt the stored procedure again. But can i lets say have some sort of loop which will pass the parameter (areaid ) into the stored procedure and get the results in c# ?

that would be i think a bit too long winded ... is that right ?

|||

Sorry, but I do not understand what you want.

|||

What I understood from the problem is that you want to execute the same query for around 300 AREAID.
If I am correct following query will solve your problem:

SELECT table1.Job_AreaId, DATEPART(hh, dbo.JobEvent.JobEventDateTime) AS hourbracket, count(ID) as NUMCOUNT
from table1 INNER JOIN table2 ON table1.JobId = table2.JobEvent_JobId
where table1.Job_AreaId in
(
SELECT the list of distinct AreaId for ex: select AreaId from MasterTable
)
Group by table1.Job_AreaId

Hope this helps you!!!

|||

Hi ,

Thanks for the response . It sure did help much appreciated

robby