Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Drag and drop query result columns in SQL Server Management Studio


By:   |   Last Updated: 2007-01-18   |   Comments (2)   |   Related Tips: More > SQL Server Management Studio

Problem

There are so many new little features in SQL Server Management Studio, finding all of them is a challenge and sometimes you just accidentally find things without even looking.  One nice feature of the query tool is the ability to display your results in a table format.  This makes it very easy to copy and paste the results for SQL Server into an Excel spreadsheet, Access table or some other application.  Although this is great there are times when it would be nice to reorder the columns in the query results.  For queries that you write this is pretty easy by just changing the column order, but if you have a query that took a long time to run or if you have no control over the output from a stored procedure or some other system function it is sometimes easier to just copy the results into Excel and then change the column order for the results. Luckily in SQL Server SQL Server Management Studio there is a new way to do this.

Solution

As mentioned before there are a lot of new little features in SQL Server Management Studio.  Once nice little change is the ability to drag your output result columns into a different location without having to rerun the query.

Here is a screenshot of a simple query where all data is pulled from the HumanResources.Employee table.  As you can see the EmployeeID is the first column and NationalIDNumber is the second column.

query window

If you left click on a column and drag the column to some other position the data output is changed without having to rerun the query.  The following screenshot shows how to select and drag the column.

query window

This next screen shot shows the query results in a different order without having to modify and rerun the query.

query window

The other nice thing about doing this reorganization of the output columns is that when you copy and paste the data the format stays the same, it does not revert back to the original format from the query output.  Here is a sample copy of the data from SQL Server into an Excel spreadsheet.

query window

 So there you have it, another nice little feature of SQL Server Management Studio.

Next Steps


Last Updated: 2007-01-18


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, May 15, 2013 - 1:32:54 PM - Greg Robidoux Back To Top

@Frank - try this

Open a new Excel sheet and select all of the cells and make the cell format TEXT.  Then copy and paste the data from SQL Server and this will treat all cells if they were TEXT instead of numbers.

You could also just do this for the columns that will be TEXT if you know ahead of time which columns will need to be TEXT.


Wednesday, May 15, 2013 - 11:27:14 AM - Frank Back To Top

As a Data Analyst at a remote offoce, I frequently am asked by my associates to extract data from the enterprise DBs and deliver results in Excel w/Pivots.  Many of the DB tables have identifiers that are numberic in content but defined as varchar data-types (such as tax or SSN IDs.  

When I copy and paste from an SSMS results grid to Excel, Excel perfers to treat the varchar numeric values as numeric rather than text.  IDs with leading zeros have the zeros suppressed.  I then need to tell Excel to change the affected cells/columns to text format, then re-paste the result set.

Any tips available to avoid this hassle?


Learn more about SQL Server tools