change column position

I have to change column position with sql or tsql.
I have a table with 3 column: column 1, column 2, column 3.
I want to move for example column 2 before column 1 or after column 3.

Can anyone help me?

tks a lot

Why? The physical order is irrelevant.

This table is created dinamically by the user. Then I have to retrieve name of column and values and I do it with a For...Next cycle. So it display the field one by one order by their phisical order.
The admin need to modify in a second moment which is the correct order to display the fields so I have to change the position of the column in the table.

Or create a view ...

In my application the admin create the table (with the fields he wanted) and the user insert the data into the table. But the admin need to change the position of the fields.

My problem is not the way to display data but the way to display the name of the field.

Ya know I don't know an easy way to do it, but I can't help but think the order of which the field data is entered in the front end and the "order" of the fields in the table is of no matter. You can't dump the values the Admin enters into variables, then in your INSERT dump the variables (in desired order) into the table?...@least that's how I handled a similar situation w/a recent Java app.

If you must though, the only thing I could think to do is create a view of the table (select the fields in the desired order), insert that view into a temp table, wipe out the old table and lay it back out again in the desired order...insert values from the temp table. Sorry, my SQL is limited, but that's all I could think of.

null, good one

i would skip the view and just define the temp table

kenda, if you need to, you can get the names of the fields from the INFORMATION_SCHEMA tables -- check Books OnLine

however, i don't think you need to

just rename your table, then define a view on it using the original name

everything should keep running exactly as before, the columns will just appear to be in a different order