Simple, but awesome…

This following tip is for working with data in Sql Server via either Query Analyzer or Sql Management Studio. It might work for other database platforms, depending on the tool that you use to run ad hoc queries. For those that are still interested, read on!

Developers – how many times have you selected some number of rows from a database to get a list of row IDs that you need to act on, only to have them presented in a column, rather than a nice comma separated list that would plug easily into a sql statement? You know the drill – select the column in the results, copy the IDs up to the query panel, and then do the comma-delete-end dance, building a comma delimited list yourself. This is fine when there’s 10 or 20 rows, but what about 100s? Sure, you can write a select statement that builds individual sql statements for each row that you need to act on, but there’s a better way…

First, grab the list of IDs and paste them into Excel (you’ll want the results to be in Grid format rather than text). Select the cell to the right of the first result, type = and click the first result. You should have something like this now (I added the column headers for clarity, they’re not required):

Once that’s done, move to the cell under the one you just edited and enter this text: =CONCATENATE(B2,”,”,A3). You may need to change the cell references if you don’t have a header row or if you’re not in the top left of your spreadsheet. You should end up with something like this:

Hit enter and you should see where we’re headed. Now grab that little box in the lower right corner of the cell you just edited and drag it down. You can see the little box in this picture:

Once you drag it to the bottom of your list of IDs, you should see this:

Look at the last row – it’s the list of IDs, all nice and comma delimited, ready to be pasted into a sql statement. I really have no idea why I didn’t think of this before, but now that I’ve found it I’ll never forget, and I’ll also never do the comma-delete-end dance again. Enjoy!

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>