SQL Server Programming Tricks: Iterating Columns


I have seen a few examples where people are trying to map columns from SQL output. In many of the examples, I see something like this:

SELECT t1.*, t2.*
FROM Table1 t1
JOIN Table2 t2
ON t1.id = t2.FKID

There are many ways to solve the issue, but I am not going to cover this as much as being more specific in SQL Queries.

The wildcard (*) is a lazy way to program SQL statements. It is useful when you are figuring something out (although TOP X becomes mandatory when working with large data sets). They are not a good practice in production code, however.

I suggested recently that someone take the time to iterate the columns in his SQL Statement and he said, but I have tons of columns. Fair enough, but since it only takes a few minutes and a couple of common tools to iterate the columns, why not take the time. In the process, you might find you are not using part of the data and you can reduce the size of the result set, which is always a good practice in my book.

Here is a “clever” trick to iterate and produce a SQL Statement.

1. Get the Columns

This is as simple as running the following commands in SQL Server:

exec sp_help Table1
exec sp_help Table2

2. Create the SQL Statement Bits

This is a bit of using a tool that someone might not expect you to use. In this case, I use Excel. NOTE that this assumes you copied to the first cell, which is A1.

  1. Copy the column result to Excel
  2. Create the following in the first row on an empty column
    =CONCATENATE(", t1.", A1)
  3. Drag the column down to the last column in the list
  4. Repeat for table2

3. Copy back to the statement

Copy the concatenated row from Excel and put it in place of t1.*. Do the same for t2.*. Delete the first comma in the list. You now have columns iterated.

Some Other Useful Tricks

To alias a column:

  1. Create another column with aliases
  2. Change the concat statement to read
    =CONCATENATE(", t1.", A1, " As ", B1)

To handle nulls as empty strings

  1. Change the concat to read
    =IF(G1 = "yes"
    , CONCATENATE("CASE WHEN ",A1, " IS NULL THEN ” ELSE ",A1, " END AS ",A1)
    , CONCATENATE(", t1.", A1))

NOTE: The breaks in the statement are for readability. You do not have to carriage return in Excel

Final Notes

Excel is a useful tool for “coding” repetitive sections of code. the fact you can create a statement and then pull down is very useful.

At some point in time I need to create a SQL stored proc that does this work for me, as there is no real need to go to Excel for this. The only thing I cannot easily do is the aliases.

Peace and Grace,
Greg

Twitter: @gbworld

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: