Display columns by order - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Display columns by order

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2012-11-09 : 11:45:19

I have a table, I want to display all columns by asc order.
What is the script?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-09 : 12:08:34

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

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2012-11-09 : 13:06:26

I have many columns. List all is not practical.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-09 : 13:37:46

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);

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-11-17 : 03:26:36

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources