Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
-
How to use column with value of N to turn 1 row into N number of rows
What you need to do is create a cursor (loop), and another cursor inside of it. Your outer cursor will grab each record. Place the values in variables. Then on the inner cursor, you need a variable (integer) that is incremented each pass. Inside of this inner cursor you will have the actual... -
SELECT statement
It's tricky, but if you create a cursor (loop) and use the system tables (which is where you find the column names, etc) you can do this. -
Limit number of records queried
select top 500 * from your_table "order by" will deterimine which 500 will be returned. -
How to return 1 RANDOM record
It's by no means fool proof, and you could argue it's hardly random. However, if there is a character field longer than 60 characters in your table, you could do something like this. select top 1 * from your_table order by substring(field1,datepart(s,getdate()),10) This orders the table... -
String format
although I'm not familiar with Oracle. It sounds like you want to use CAST. syntax would be something like: CAST(your_field as CHAR(100)) of course, you would pick whatever datatype and length fits your needs. -
Consolidating Records
Assuming you're using MS SQL Server....there is a function in transact SQL called "pivot table" that works for this type of situation. It busts out the values of a field into columns, and aggregates the data. I'm not sure of the syntax, however. Read up on it in Books Online. -
Update based on max time less than current row
as rac2 stated, more info would be helpful. Sounds like you could use a trigger to accomplish your goal. Although, I'm operating under the assumption you're using MS SQL Server. First, you need an auto-incrementing field. In this case I'll call it ID. And since you didn't supply a table... -
Replace substring of field
Here's how I would write it. Update component set ComponentTag = ('010' + substring(ComponentTag,4,4)) where substring(ComponentTag,1,3) = '001' --this is assuming your ComponentTag field is only 7 characters long. Hope this helps...
