November 22, 2011 at 1:34 pm
We are doing a large data conversion using a query. Works fine.
Problem is that there are over 500 fields and it is very hard to keep track on which filed we are on.
Is there a way in SQL to have as part of my query the column names enumerated (numbered in order)?
November 22, 2011 at 1:39 pm
What is the overal task? Why do you need to enumerate through the columns?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 22, 2011 at 1:49 pm
to match up with an excel spreadsheet showing us the data translations.
Long story but we are using a query to populate a table then doing a data dump from the table. Everything works fine, but it would be really nice in our massive query if we could enumerate the column names to match the enumerated spreadsheet.
November 22, 2011 at 2:00 pm
I'm still not clear on what you mean.
Are you saying you are using a query to populate a table (that holds table/column names) then iterating through that and selecting data out dynamicly? but at any rate, this might help....????
SELECT
t.name, c.column_id, c.[name] AS columnName
FROM sys.tables AS t
INNER JOIN sys.columns AS c
ON t.object_id = c.object_id
WHERE t.[type] = 'U'
ORDER BY t.[name], c.column_id
it's hard to give a really solid answer without a better description of the goal/process. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 22, 2011 at 2:06 pm
krypto69 (11/22/2011)
...Is there a way in SQL to have as part of my query the column names enumerated (numbered in order)?
Simple answer: no. And that's just because there is no predefined, deterministic "order of columns". The order in which columns are selected determine the order of the columns in the result set. This may vary from query to query without changing the underlying structure.
There is an internal number to reference the column name (column_id in sys.columns). But neither does this number needs to be sequential nor is it guaranteed to be always linked to the same column name. Example: if someone decided to use SSMS and add a column "between" two existing columns, the column_id will be different than before. (Please note "between" refers to the visual appearence in SSMS. The internal logical order of columns has nothing to do with the order of columns at the presentation layer.)
One -rather ugly- solution could be a staging table with numbered column names. But like I said: it's ugly and should be used -if at all- for a temporary staging table only.
November 22, 2011 at 2:24 pm
krypto69 (11/22/2011)
to match up with an excel spreadsheet showing us the data translations.Long story but we are using a query to populate a table then doing a data dump from the table. Everything works fine, but it would be really nice in our massive query if we could enumerate the column names to match the enumerated spreadsheet.
probably missing the point here...but are you running a query to create a table that you then dump (assume ....load into excel and compare against another excel file...???)...is there any reason why you cannot provide "alias" names to your table and have the same names as column headers in the exg excel spreadsheet?...just a thought;-)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 22, 2011 at 2:36 pm
Thanks guys..
sounds like there's no real way to enumerrate...
yeah guess I could always add an extra numeric alias ..but was hoping there was something intrinsic
November 22, 2011 at 2:45 pm
krypto69 (11/22/2011)
Thanks guys..sounds like there's no real way to enumerrate...
yeah guess I could always add an extra numeric alias ..but was hoping there was something intrinsic
Well, that's not true. You can enumerate using the column_id, or an alias, or identity field from previous "select into" query, but you just can't guarantee the ordering to match the storage etc...
But then why would you? if your simply iterating to build a select or store data, what does the order matter?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply