Build SQL from a table

Transact-SQL
1

I have a table called LOG_SITES, with a column called SITE_NAME.

The SITE_NAME is actually a table name that I want to use as Tab1. First I want to find its column names other than TmStmp. There is a variable number of column names.

Next I want to build SQL that ?select col1, col2, ... from Tab1".

My requirement is going to get more detailed, but if I can get the basics down on what I can and cannot do, I can build on it.

Any insights would be greatly appreciated.
Sherrie

2

Here is an example to get you started. This is not perfect even if you stretch your imagination, so be wary. You can print the statements created to see if it is doing what you want it to do.

DECLARE 
	@TableName VARCHAR(256),
	@sql NVARCHAR(4000);

-- get the tablename. Assumption here is that the query returns exactly one row
SELECT @TableName = SITE_NAME FROM dbo.LOG_SITES WHERE somecondition = someother;

-- build the column list. assumes the table is in schema dbo.
SELECT @sql = ',' + name
FROM
	sys.columns 
WHERE 
	OBJECT_NAME(OBJECT_ID) = @TableName
FOR XML PATH('');

-- this assumes the schema name is dbo.
SELECT @sql = 'SELECT ' + STUFF(@sql,1,1,'') + ' FROM ' + @TableName;

-- run the query.
EXEC sys.sp_executesql @sql;
1 Like
3

The plot thickens, as you can see I’m trying to build requirements as we go. Ultimately they’d like a view that does an unpivot on all the data and has a row for each column. The view will look like

[/uploads/sqlteam/original/2X/2/2bef2730345b06c488127dfd83d0e3d113a82de1.png]

This will take a little pondering but I have a good start.