Alphabetical order

Alphabetical order

Hi, if I have a table with the columns:

NAME       CODE    SUPPORTCODE   ADDRESS  CUSTOMERNAME  ... ... ...

Can I do a select that will return me the columns on alphabetical order???

ADDRESS  CODE    CUSTOMER   NAME    SUPPORTCODE   ....

date: Thu, 7 Aug 2008 15:25:50 -0300 author: Paulo

Re: Alphabetical order

Why don't you just create a view that hard-codes the columns in that order?
You can do this with dynamic SQL of course, but columns are just columns,
and order shouldn't matter, so SQL Server doesn't have a magic flag to say
structure the resultset with the columns in this or that order...

On 8/7/08 2:25 PM, in article uHNq2tL#IHA.5056@TK2MSFTNGP06.phx.gbl, "Paulo"
 wrote:

> Hi, if I have a table with the columns:
> 
> NAME       CODE    SUPPORTCODE   ADDRESS  CUSTOMERNAME  ... ... ...
> 
> Can I do a select that will return me the columns on alphabetical order???
> 
> ADDRESS  CODE    CUSTOMER   NAME    SUPPORTCODE   ....
> 
>

date: Thu, 07 Aug 2008 14:40:20 -0400 author: Aaron Bertrand [SQL Server MVP] a

Re: Alphabetical order

Try:

SELECT ADDRESS, CODE, CUSTOMER, NAME, SUPPORTCODE
FROM Table


Plamen Ratchev
http://www.SQLStudio.com

date: Thu, 7 Aug 2008 14:40:48 -0400 author: Plamen Ratchev

Re: Alphabetical order

Try:

SELECT ADDRESS, CODE, CUSTOMER, NAME, SUPPORTCODE
FROM Table


Plamen Ratchev
http://www.SQLStudio.com

date: Thu, 7 Aug 2008 11:44:54 -0700 (PDT) author: unknown

Re: Alphabetical order

If the table has 50 fields the only way is writing one by one?

"Select FieldA,FieldB,FieldC,FieldC, ..., ... from Table" ???????


"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> escreveu na 
mensagem news:C4C0B9D4.EBDD%ten.xoc@dnartreb.noraa...
> Why don't you just create a view that hard-codes the columns in that 
> order?
> You can do this with dynamic SQL of course, but columns are just columns,
> and order shouldn't matter, so SQL Server doesn't have a magic flag to say
> structure the resultset with the columns in this or that order...
>
>
>
>
>
> On 8/7/08 2:25 PM, in article uHNq2tL#IHA.5056@TK2MSFTNGP06.phx.gbl, 
> "Paulo"
>  wrote:
>
>> Hi, if I have a table with the columns:
>>
>> NAME       CODE    SUPPORTCODE   ADDRESS  CUSTOMERNAME  ... ... ...
>>
>> Can I do a select that will return me the columns on alphabetical 
>> order???
>>
>> ADDRESS  CODE    CUSTOMER   NAME    SUPPORTCODE   ....
>>
>>
>

date: Thu, 7 Aug 2008 15:46:53 -0300 author: Paulo

Re: Alphabetical order

> If the table has 50 fields the only way is writing one by one?

You can do this to make your hard-coding easier:

SELECT name + ','
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.TableName')
ORDER BY name;

Take the result, drop the last comma, and paste it into the query window.

Again, I'm not sure why the query itself needs to present the columns in 
this order.  The consuming application can certainly consume the columns in 
the resultset in whatever order it wants, and it automatically gets metadata 
about the columns first (which T-SQL does not).  My guess is because typing 
50 columns is hard, that you are also using ordinal position in your 
consuming application(s) too...

date: Thu, 7 Aug 2008 15:02:16 -0400 author: Aaron Bertrand [SQL Server MVP] a

Re: Alphabetical order

"Invalid object name 'sys.columns'."

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> escreveu na 
mensagem news:OJQe9$L%23IHA.1512@TK2MSFTNGP06.phx.gbl...
>> If the table has 50 fields the only way is writing one by one?
>
> You can do this to make your hard-coding easier:
>
> SELECT name + ','
> FROM sys.columns
> WHERE [object_id] = OBJECT_ID('dbo.TableName')
> ORDER BY name;
>
> Take the result, drop the last comma, and paste it into the query window.
>
> Again, I'm not sure why the query itself needs to present the columns in 
> this order.  The consuming application can certainly consume the columns 
> in the resultset in whatever order it wants, and it automatically gets 
> metadata about the columns first (which T-SQL does not).  My guess is 
> because typing 50 columns is hard, that you are also using ordinal 
> position in your consuming application(s) too...
>

date: Thu, 7 Aug 2008 16:05:02 -0300 author: Paulo

Re: Alphabetical order

"Invalid column name 'object_id'."

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> escreveu na 
mensagem news:OJQe9$L%23IHA.1512@TK2MSFTNGP06.phx.gbl...
>> If the table has 50 fields the only way is writing one by one?
>
> You can do this to make your hard-coding easier:
>
> SELECT name + ','
> FROM sys.columns
> WHERE [object_id] = OBJECT_ID('dbo.TableName')
> ORDER BY name;
>
> Take the result, drop the last comma, and paste it into the query window.
>
> Again, I'm not sure why the query itself needs to present the columns in 
> this order.  The consuming application can certainly consume the columns 
> in the resultset in whatever order it wants, and it automatically gets 
> metadata about the columns first (which T-SQL does not).  My guess is 
> because typing 50 columns is hard, that you are also using ordinal 
> position in your consuming application(s) too...
>

date: Thu, 7 Aug 2008 16:08:34 -0300 author: Paulo

Re: Alphabetical order

I got it:

SELECT name + ','
FROM syscolumns
WHERE [id] = OBJECT_ID('dbo.Agenda')
ORDER BY name;

Very good man...

Many thanks!
"Paulo"  escreveu na mensagem 
news:%231btvFM%23IHA.4476@TK2MSFTNGP05.phx.gbl...
> "Invalid column name 'object_id'."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> escreveu na 
> mensagem news:OJQe9$L%23IHA.1512@TK2MSFTNGP06.phx.gbl...
>>> If the table has 50 fields the only way is writing one by one?
>>
>> You can do this to make your hard-coding easier:
>>
>> SELECT name + ','
>> FROM sys.columns
>> WHERE [object_id] = OBJECT_ID('dbo.TableName')
>> ORDER BY name;
>>
>> Take the result, drop the last comma, and paste it into the query window.
>>
>> Again, I'm not sure why the query itself needs to present the columns in 
>> this order.  The consuming application can certainly consume the columns 
>> in the resultset in whatever order it wants, and it automatically gets 
>> metadata about the columns first (which T-SQL does not).  My guess is 
>> because typing 50 columns is hard, that you are also using ordinal 
>> position in your consuming application(s) too...
>>
>
>

date: Thu, 7 Aug 2008 16:11:19 -0300 author: Paulo