Automatically rearrange columns in resultset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Automatically rearrange columns in resultset

Author

Topic

Ambiance44
Starting Member

16 Posts

Posted - 2009-05-15 : 13:08:40

Hello,

I am writing a query that joins several tables. Is it possible to automatically rearrange the columns alphabetically in the resultset?

So I would like the resultset of a statement like the following to be displayed with column names arranged alphabetically


SELECT item.id AS itemId, item.name, item.desc, item.price, ...,
itemCat.id AS itemCatId, itemCat.name AS categoryName, itemCat.desc AS categoryDesc, ...
FROM itemCat
LEFT JOIN item ON item.id = itemCat.id

I understand that I can rearrange the columns in the SELECT statement, but sometimes there are too many column names to manually rearrange in a SELECT statement.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-15 : 13:17:00

nope you cant automatically change the column names in select list unless you use dynamic sql. Not sure why you want columns in list to be in alphabetical order though

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-15 : 13:33:54

You should never rely on a column's position in a result set, always reference it by name.

Ambiance44
Starting Member

16 Posts

Posted - 2009-05-15 : 14:17:07

Visakh16: Thanks for the response.

robvolk: The reason I wanted to rearrange the columns is not to rely on it for handling data, but for debugging/troubleshooting when inspecting large SELECT queries in Management Studio. The number of columns returned by some of the queries need a lot of scrolling, and finding a column by its name is much easier if columns are arranged alphabetically.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-15 : 14:21:39

but in management studio you can always see column headers in resultset wont you? i didnt understand how it becomes confusing in finding out each columns name and data

Ambiance44
Starting Member

16 Posts

Posted - 2009-05-15 : 14:46:34

Yes, Management Studio shows column names and data. What I mean is: if a query returns 25 columns and I'm looking for specific columns, it is much easier to scroll directly to it if columns are arranged alphabetically.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-15 : 14:52:18

you can always give column names in select list in the order you want it to see

Ambiance44
Starting Member

16 Posts

Posted - 2009-05-15 : 15:00:35

quote:
Originally posted by Ambiance44

I understand that I can rearrange the columns in the SELECT statement, but sometimes there are too many column names to manually rearrange in a SELECT statement.

But thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources