SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Display columns by order
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zhshqzyc
Posting Yak Master

240 Posts

Posted - 11/09/2012 :  11:45:19  Show Profile  Reply with Quote
I have a table, I want to display all columns by asc order.
What is the script?

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/09/2012 :  12:08:34  Show Profile  Reply with Quote
This
SELECT
	co1,
	col2,
	-- etc...
	colN
FROM
	YourTable
ORDER BY
	col1 ASC,
	col2 ASC,
	-- etc...
	colN ASC

ASC is the default, so you can omit it. Also, you can refer to columns in the select list by their ordinal number (in the select)
SELECT
	co1,
	col2,
	-- etc...
	colN
FROM
	YourTable
ORDER BY
	1,2,3,4,
	-- etc
	N
Go to Top of Page

zhshqzyc
Posting Yak Master

240 Posts

Posted - 11/09/2012 :  13:06:26  Show Profile  Reply with Quote
I have many columns. List all is not practical.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/09/2012 :  13:37:46  Show Profile  Reply with Quote
Listing the columns that you want to order by is the ONLY WAY in SQL Server to guarantee ordering of the result set.

Not sure what you mean by "not practical". If the concern is one of manually typing in the column names, you can write a simple query against INFORMATION_SCHEMA.COLUMNS view to generate the list; for example
SELECT ','+COLUMN_NAME FROM INFORMATION_SCHEMA.[COLUMNS] 
WHERE TABLE_NAME = 'YourTabelNameHere' ORDER BY ORDINAL_POSITION
FOR XML PATH('')


If you want to order by ordinal position, you can generate the list of ordinal positions from a query
;WITH N(n) AS
( SELECT 1 UNION ALL SELECT n+1 FROM N WHERE n < 1000)
SELECT ',' AS [text()],n AS [text()] FROM N 
FOR XML PATH('') OPTION (MAXRECURSION 0);
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22774 Posts

Posted - 11/17/2012 :  03:26:36  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by zhshqzyc

I have many columns. List all is not practical.


How many columns? Is the query using dynamic sql?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000