Looping thru columns in a table?
-
Can I loop thru the columns of a table using some kind of index instead of column names? Something like SELECT table.column(1) or such? We have a systable syscolumns, I would suspect it can, but how to refer to a column such way?
Greetz,
Hans Brouwer -
Are you experimenting with technique? or trying to manipulate information on columns? building a script? perhaps trying to do column operations without using set-based SQL? What's the objective here, Hans?
-
This query gives you the column names for a given table-name.
select sc.name
from syscolumns sc
join sysobjects so on sc.id=so.id
where so.name='table-name' and so.xtype='U'
order by colid
-
Instead of telling us how you want to do something, it would be much more beneficial to yoru cause if you told us what you wanted to do and why.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sorry for the delay, did not receive notifications for this thread, cause I had deleted my subscriptions...
On-topic:
I am working on a script, in which I built a tmpTable, for which I do not know in advance howmany columns I am going to need. I have to search column values and react on them(filling a Flexgrid in VB). I was hoping I can loop thru the columns untill I reach a NULL value or the last column of the current record.
Some records might have values in 3 columns, some in 5 or whatever. Hope I make myself clear?
Greetz,
Hans Brouwer -
Hans,
that makes some sense, but a recordset or a table is a fixed width item. So if some rows have 3 column values and some have 5, it doesn't matter. You need 5 in the final result. Why not get all the data, then process this on the VB side? This type of moving through the grid approach is horrible inefficient in SQL Server.
If you really need to loop columns, you'd need to use syscolumns to figure out how many are in a table. But it's the same for every row.
-
You are right Steve, I don't think this is going to be done in SQL anyways, so it's a moot point now. Still, I wondered if I could refer to a column instead of SELECT ColumnName From TableName
like
SELECT Column(1) From TableName
where Column(1) is the first(or second) column in this table.
Greetz,
Hans Brouwer -
You can reference columns by number from within ADO.
-
Hey Mike it is better to use the INFORMATION_SCHEMA objects as is supported by MS
select COLUMN_NAME from information_schema.columns where TABLE_NAME = 'title' ORDER BY ORDINAL_POSITION
-
No you cannot use ordinal position in the SELECT column list from a SQL SELECT query just in the ORDER BY which is the oridinal of the column list from the select.
As noted in ADO you can reference the columns by ordinal position without calling by name.
-
Should I have said ADO.NET?
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply