May 18, 2006 at 12:47 am
I am trying to create a tempory table to provide summary information that can contain multipule columns based on a variable number of criteria. I want to do this within TSQL and save as a Stored Proc so that the result set can be called from a variety of sources. I have no problem performing this task outside SQL Server using ADO but can't seem to find a way to perform the same functionality within TSQL. In its simplest form the task is to take summarized rows of data and flip them into colums in a tempory table that I can update with additional columns of data from subsequent queries. This task takes the form of shifting rows of data into coloumns of data.
If it is not possible to create a table using variables for the column names then any other ideas on how to accomplish the desired results would be greatly appreciated.
May 18, 2006 at 1:37 am
Hi Mark,
You can create a tabel defining column names using variables. See below example:
DECLARE @W_SQL AS NVARCHAR(2000)
DECLARE @W_ColName_1 AS NVARCHAR(50)
DECLARE @W_ColName_2 AS NVARCHAR(50)
SET @W_ColName_1 = 'Column_a' SET @W_ColName_2 = 'Column_b'
SET @W_SQL = 'CREATE TABLE abc ('
SET @W_SQL = @W_SQL + @W_ColName_1 + ' VARCHAR(20) NULL, '
SET @W_SQL = @W_SQL + @W_ColName_2 + ' INTEGER NULL 'SET @W_SQL = @W_SQL + ')'
EXECUTE SP_EXECUTESQL @W_SQL
I hope this will work for you.
May 18, 2006 at 1:56 am
Aarez,
That was exactly what I needed thank you.
Mark
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply