order table by column names + asp.net

[kev510]

Hi everyone,

I am trying to program an asp.net webpage that displays data from a table in SQL server 2000, but it needs to display the tables with the columns in alphabetical order. The problem is that the webpage displays about 20 tables (with up to 30 columns per table). There has to be an easier way to sort these columns.



Example -
Table contains AColumn, CColumn, BColumn, DColumn.

The webpage should display -
AColumn, BColumn, CColumn, DColumn

Any help would be appreciated! Thank you!

 

[JoeAtWork]

Write your SELECT statement to return the columns in whatever order you desire.

SELECT AColumn, BColumn, CColumn FROM ProbablyNotNormalized

 

[kev510]

It's too much work for me to write all those columns out. As I said, there are about 20 different tables (with up to 30 columns per table). There has to be an easier way to sort these columns.

 

[JoeAtWork]

The only trick I can think of is to read the field names from system tables (I'm not sure if that's possible) and build your SQL statement dynamically. However, that's a lot of extra overhead to your queries just for the sake of saving your fingers some typing.

What you might do instead is create Views of your tables with the columns ordered as you want. Then at least you only have to type (or click on) the field names once, and from then on you can just use the views.

 

[jbenson001]

You'd have to read from the systm tables or view, as Joe stated. However, as a web programmer myself, I would seriously rethink your design. What you propose is way too much data to show, readably, on a page. And no user wants to sort through that much data anyway.

 

[genomon]

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourtable'

If you are just too tired at the end of the day to type out all those column names, the code above will return them for you. It is then a simple matter of copying and pasting into a text editor, and add the commas & spaces. Hot Tip: I'm so lazy I created a macro in the text editor that goes to the end of the field name, puts a comma and space there, and triggers the delete key to bring the next fieldname up from the line below (and assigned it to a hotkey). It probably took me more keystrokes to post this than you will spend on writing your code.
Good Luck!

"No matter what happens, somebody will find a way to take it too seriously."

 

[SQLSister]

Also you can drag column names over from the object browser. Saving keystrokes is a pretty poor reason for not doing your job correctly. And honestly I can't think of any normalized data structure where anyone would want the column names in alphabetical order. Usually people want the data they use the most or which is most important first. If I was querying a parts list, I would want to see part_number before color for instance.

"NOTHING is more important in a database than integrity." ESquared

 

[PWise]

set result options to results to text and print colums heders no and run this code for each table that you need and copy and paste

Declare @tablename varchar(50)

Set @tablename = 'yourtablename'
select col from(
Select 'a' as r, 'Select ' as col
union
select * from(
				SELECT top 1  'b' as r ,table_name+'.'+COLUMN_name as col 
				FROM INFORMATION_SCHEMA.COLUMNS
				WHERE TABLE_NAME = @tablename 
				order by table_name+'.'+COLUMN_name
				union
				SELECT top 100 percent 'c' as r ,','+table_name+'.'+COLUMN_name as col 
				FROM INFORMATION_SCHEMA.COLUMNS
				WHERE TABLE_NAME = @tablename 
				and table_name+'.'+COLUMN_name not in 
					(SELECT top 1  table_name+'.'+COLUMN_name as col 
					 FROM INFORMATION_SCHEMA.COLUMNS
					 WHERE TABLE_NAME = @tablename 
				     order by table_name+'.'+COLUMN_name)
 )col
union
Select 'd' as r, ' From ' + @tablename)dt
order by r,col

 

[PWise]

the following will print the select statment on 1 line

Declare @tablename varchar(50)

Declare @Select varchar(8000)
set @select =''

Set @tablename = 'Bills'
Select @select=@select+col from(
select top 100 percent col from(
Select 'a' as r, 'Select ' as col
union
select * from(
				SELECT top 1  'b' as r ,table_name+'.'+COLUMN_name as col 
				FROM INFORMATION_SCHEMA.COLUMNS
				WHERE TABLE_NAME = @tablename 
				order by table_name+'.'+COLUMN_name
				union
				SELECT top 100 percent 'c' as r ,','+table_name+'.'+COLUMN_name as col 
				FROM INFORMATION_SCHEMA.COLUMNS
				WHERE TABLE_NAME = @tablename 
				and table_name+'.'+COLUMN_name not in 
					(SELECT top 1  table_name+'.'+COLUMN_name as col 
					 FROM INFORMATION_SCHEMA.COLUMNS
					 WHERE TABLE_NAME = @tablename 
				     order by table_name+'.'+COLUMN_name)
 )col
union
Select 'd' as r, ' From ' + @tablename)dt
order by r)dt
Select @select

 

[kev510]

thank you so much for your answers everyone!!