Another Recruiter Asks a SQL Question

By Bill Graziano on 7 March 2001 | Tags: Queries

Sandeep writes Hi IT Guys, during an interview, a recruiter asked me "How can you retrieve the first n number of fields using sql statement withought using field names?" ex. Table A contains 50 fields and I want to select first 10 fields. I don't want to mention all the 10 fields name. I love these questions!

We dealt with a recruiter question once before and had fun with it. This is the first recruiter question since then so I thought I'd give it a shot. I'll tell you how I came up with a solution built entirely on articles published on SQLTeam! Let's see if anyone can do better.

Now my job seeking reader didn't tell me (and I asked) what database he's using so I'm going to assume it's Microsoft SQL Server 7.0. However I'll also try to make my solution as ANSI standard as possible. I'll also use the pubs database for this solution.

After reading this question it was pretty obvious we'd have to use some dynamic SQL. Dyamic SQL involves building a string that is valid SQL statement and then executing the contents of the string using EXEC. If you read the FAQ you'll find a couple of articles listed with examples. I looked through Books Online and it didn't mention anything about ANSI compliance with respect to EXEC. However I'm fairly certain most major databases have some way to execute a string that contains a SQL statement.

I knew I also needed to get the names of the tables. You can do that using the INFORMATION SCHEMA views. These are a series of ANSI SQL-92 standard views that you can query to determine your database structure. For example this query:

SELECT 	*
from	INFORMATION_SCHEMA.Columns
where	table_name = 'authors'

will return all the information about all the columns in the authors tables. We can get the first ten column names using this query:

SELECT 	column_name
from	INFORMATION_SCHEMA.Columns
where	table_name = 'authors'
and	ordinal_position <= 10

Now I need to turn that into someting like au_id, au_lname, . . . so I can build my SELECT statement. Fortunately we have a great article on turning a series of rows into a list of comma separated values (CSV). We also have a few other articles on dealing with CSV strings. The article uses the COALESCE function to build the string. I'm not entirely sure about the ANSI-ness of COALESCE but I seem to remember it in there. Maybe some one will submit a link to the ANSI SQL-92 standard.

Which leaves me with this script:

set nocount on

Declare @TableName varchar(128), @ColumnList varchar(1000)
Declare @SQL varchar(1000)
set @TableName = 'authors'

SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name 
FROM INFORMATION_SCHEMA.Columns
WHERE Ordinal_position <= 10
and table_name = @TableName

Set @SQL = 'Select ' + @ColumnList + ' from ' + @TableName

Exec (@SQL)

I'm pretty sure this isn't what the recruiter had in mind. They always want some three line simple SELECT statment. The only topic I really don't have an article about is declaring, setting and using variables. Oh and the syntax of a SELECT statement. ButI do have some links to them.

You should be able to copy and paste my script into Query Analyzer just fine. So there's an answer for this recruiter using mostly articles from SQLTeam. My new tag line should say "Read SQLTeam. Get a job."

If you have an alternative or better solution please post it in the forums or after this article. I'm curious to see what someone else might come up with. Good luck with that next interview.