How to change column order of a table

Hi,
I need to modify column order of some of my sql server 2k tables
because i want to keep related columns together. How can i do this?
Thanks
Nikhilcreate a view with the columns in the order you want

rudy|||Why?

If you really need to do it.

create an new table as you want the columns to be.

Copy the data into it.

rename the old table to a different name.

rename the new table to the old name.

Fix all the Foreign keys to point to the correct table.

and pray it works!!!

I would suggest getting an book on SQL design because there is normaly no good reason to change the column order of the table.

Tim S|||It's very simple use Enterprise Manager -> Tables _> Design Table -> and use drag and drop to move one column in the position you want (it' necessary to select the row of the column in order for drag and drop to function)

Beware, this only can be done in SQL Server 2k not in any previous versions of SQL Server.

Anyway to move a column has a meaning only for a better visualisation of the table design, because the order of columns, or the phisical order of rows in the table has no importance (it's a base rule of relational database).
But, if you decide to move one column before another, you can do it nothing is going to change within your application.

In fact, there is one problem in SQL 2k. If you are changing the position of a column and for that table you have a trriger that use the function COLUMNS_UPDATED(n) (where n is the no of a row in the table) and after your switching positions of columns the positin of the column implied in COLUMN_UPDATED function changes you should modify your trriger in order to work corectly.

IONUT

How to callapse rows together

I'm need to collaspe a few rows together and output one row per group. The rows come from a flat file and are already sorted by orderid. Basically these are transactions with just the changed fields in them all unchanged fields are null (blank). They need to collapsed down to show the final state of all changed fields per order. Obviously the aggregate transform won't work in this situation since the goal is to collaspe instead of aggregate. In a language, I would normally buffer the previous row and apply the logic as needed until the orderid changed and then write out the new row. Then input file has millions of rows so I also didn't want to cursor through it updating individual rows in a table.

Example:

Orderid StatusCd OrderDt OrdrAmt

1 A 01/01/06 10

1 B

2 X 02/02/06 20

3 03/03/06 30

3 03/04/06

Collapse as

1 B 01/01/06 10

2 X 02/02/06 20

3 03/04/06

Danny,

The logic that you say you would apply in a programming language can be applied in exactly the same in SSIS by using a script component. I recommend you take a look at building an asynchronous script component.

-Jamie