Introduction

The other day I was presented with a peculiar problem from one of our developers.  “Do you know how to write a SQL statement that will select the data from the top 10 columns in a table”?  This took a few moments to register; you want the top 10 columns from a table and not the rows.  My next question was, “Why?”  I know a few of my DBA buddies who would laugh at this type of question, but I chose to take this problem on.

Means To an End

Since the TOP clause provides the first set of rows in a column, I knew the only path to this solution would be through the metadata of SQL Server.  My table of choice for this solution will be INFORMATION_SCHEMA.COLUMNS, which holds the key ingredient to ranking columns in a table.  You guessed it, the ordinal position.  Since ordinal position dictates the column order for columns in a table physically, this is where I chose to base my ranking of TOP columns.  Let’s not forget that our developer not only wants the top columns from the table, he also wants the data in these columns

Our Solution

The first piece of the puzzle here is to capture our TOP column names in a form that can be used dynamically later on in the script.  For this task we will simply create a variable to hold the output of the column names captured from INFORMATION_SCHEMA.COLUMNS.  We will also use the ORDER BY statement to rank our columns for the TOP clause.

Step 1:  Build the column list

DECLARE @columnName VARCHAR(4000)
--This seeds the variable for use. 
SET @columnName = ''
SELECT TOP 10 @columnName = @columnName + '[' + COLUMN_NAME + '], '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'
ORDER BY ORDINAL_POSITION ASC

This statement is a very simple, cursor free solution that will create a comma separated list of column names based on the TOP clause.  You will notice that I have also wrapped brackets around the column name to allow for the inevitable key word or column name with illegal characters in them.  By concatenating the variable @columnName in the SELECT statement, we eliminate the need for cursors here.  Our next step is to stage the @columnName variable for use in a dynamic SQL statement.

Step 2: Prep the comma separated column name list

SUBSTRING(@columnName, 1, DATALENGTH(@columnName) - 2)

We will need to make use of the SUBSTRING function to remove the trailing comma and space from the very last column name in the list. 

Step 3: Putting it all together

/***********************************************************************************************
AUTHOR: Tim Parker
DATE: 8/11/2011
PURPOSE:
This script will return the TON N columns for a given table, and then will dynamically
build and execute a SELECT statement using these columns against the table.
***********************************************************************************************/DECLARE @columnName VARCHAR(4000),
@dyanamicSQL VARCHAR(8000),
@tableName VARCHAR(255)
--This seeds the variable for use. 
SET @columnName = ''
--Supply the table name to reference.
SET @tableName = 'MyTableName'
--You must manually set the TOP N columns.
SELECT TOP 10 @columnName = @columnName + '[' + COLUMN_NAME + '], '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName
AND TABLE_SCHEMA = 'DBO'
ORDER BY ORDINAL_POSITION ASC
--You will need to modify the SELECT statement accordingly here. 
SET @dyanamicSQL = 'SELECT ' + SUBSTRING(@columnName, 1, DATALENGTH(@columnName) - 2) + ' FROM ' + @tableName +
EXEC( @dyanamicSQL )

The completed solution allows the user to supply a table name as the parameter, and set the TOP N columns they will need for the SELECT statement.  This completed solution allows the developer to select the data from the top 10 columns in a table.  Since no two situations are the same, consider this solution a good base line for solving other problems that may rely on ranking columns in a table.

MSDN Reference

TOP Clause

http://msdn.microsoft.com/en-us/library/ms189463.aspx

I hope you find this helpful.