Is it possible to create a temp table with dynamic columns?
-
Hi,
I need to create a temp table in my stored procedure with some parameters passed in.
The parameters will be @StartDate DateTime and @Mode Char(10)
So, if @Mode = 'Weekly' and @StartDate = '2005/08/01', I want to create a temp table with the following columns.
Create table #Weekly (Desc varchar(50), 01Aug char(10) Null, 02Aug char(10) Null, 03Aug char(10) Null, 04Aug char(10) Null, 05Aug char(10) Null, 06Aug char(10) Null, 07Aug char(10) Null)
I want to have column names with the Date.
So, if @Mode = 'Monthly' I will have columns created based on the month of the @StartDate
Is this even possible?
-
I'm guessing here but that I think that should be achievable using dynamic SQL.
It won't like the zeros at the start of the column names though, you'll have to wrap them in square brackets.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Hi Jamie,
What's dynamic SQL? Is it possible if you can show me an example? I am fine with using square brackets in my column name.
Thanks.
-
I guess Jamie means building an SQL statement in a string and then executing it, like the following:
declare @STR varchar(100)
set @STR = 'select * from sysobjects'
exec(@str)
But you should be aware that - as far as I know - if you create a temp table in your SQL string and execute this string dynamically, the temp table won't exist outside your string.
I think you should consider using another db design - is this possible?
-
This should get you started. You can modify it to work for any month!
create procedure MyProc
as
declare @str varchar(8000)
declare @counter integer
set @counter = 1
set @str = 'create table MyTable ([desc] varchar(50)'
while @counter <= 31
begin
set @str = @STR + ', [' + RIGHT('0' + cast(@counter as varchar(2)), 2) + 'Aug] char(10) NULL'
set @counter = @counter + 1
end
set @str = @STR + ')'
exec (@str)
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Hi Jesper,
I want to be able to pass the data retrieved to .Net which will (hopefully) display the results somewhat like the following.
Vessel/Desc 1-Aug 2-Aug 3-Aug 4-Aug 5-Aug 6-Aug 7-Aug + Vessel1 - Vessel2 Orders Quote Maintenance - Vessel3 Orders Quote The users will have to select the View Mode which is : 1)Weekly, 2) Monthly, 3)Quarterly and also state the Start Date for which they wanna see the data.
I think the work ard to creating temp table in SQL string is to use ##MyTable as tablename. Globalise it?
Thanks.
-
You are right, ##MyTable works. Learned a new trick
Is it necessary to create a temp table and insert data into it? Why not simply write a select statement that returns all the data you need? If you post table definitions and maybe some sample data, I am sure someone can help you write such a statement
-
I cannot see my own post, so here we go again
You are right, ##MyTable works. Learned a new trick
Is it necessary to create a temp table and insert data into it? Why not simply write a select statement that returns all the data you need? If you post table definitions and maybe some sample data, I am sure someone can help you write such a statement
-
##MyTable in a stored procedure. What will happen if more than one call at a time to the stored procedure?
Another question how did you make thread inside a thread. Or my browser behaves odd?
Regards,
gova -
I think you are right - global tempo tables shouldn't be used for this purpose
To answer your second question, I don't know
I just pressed "quote" and typed my reply 
But I think I have run into a bug in the software behind this site -
Try this
DECLARE @Mode varchar(10), @StartDate datetime, @sql varchar(4000)
SET @Mode = 'Weekly'
SET @StartDate = '2005/08/01'
IF @Mode = 'Weekly'
BEGIN
SET @sql = 'ALTER TABLE #temp ADD '+ '[' + REPLACE(CONVERT(char(6),@startdate,113),' ','') + '] char(10) null'
SELECT @sql = @sql + ',[' + REPLACE(CONVERT(char(6),@startdate+number,113),' ','') + '] char(10) null'
FROM master.dbo.spt_values WHERE type='P' AND number between 1 AND 6
END
CREATE TABLE #temp ([Desc] varchar(50))
EXECUTE (@sql)
INSERT INTO #temp ([Desc],[01Aug],[02Aug],[03Aug],[04Aug],[05Aug],[06Aug],[07Aug]) values ('TEST','01','02','03','04','05','06','07')
SELECT * FROM #temp
DROP TABLE #temp
Far away is close at hand in the images of elsewhere.
Anon. -
Hi David,
Thanks, I will try to see if this will work for me.
Try this
DECLARE @Mode varchar(10), @StartDate datetime, @sql varchar(4000)
SET @Mode = 'Weekly'
SET @StartDate = '2005/08/01'
IF @Mode = 'Weekly'
BEGIN
SET @sql = 'ALTER TABLE #temp ADD '+ '[' + REPLACE(CONVERT(char(6),@startdate,113),' ','') + '] char(10) null'
SELECT @sql = @sql + ',[' + REPLACE(CONVERT(char(6),@startdate+number,113),' ','') + '] char(10) null'
FROM master.dbo.spt_values WHERE type='P' AND number between 1 AND 6
END
CREATE TABLE #temp ([Desc] varchar(50))
EXECUTE (@sql)
INSERT INTO #temp ([Desc],[01Aug],[02Aug],[03Aug],[04Aug],[05Aug],[06Aug],[07Aug]) values ('TEST','01','02','03','04','05','06','07')
SELECT * FROM #temp
DROP TABLE #temp
-
Ok, I don't really have the exact table definition but, I suspect that it will be something similar to this.
TABLE JobHeader (
[JobNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[JobDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BillCustomer] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Salesperson] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Maintenance] [bit] NULL )
TABLE [JobLines] (
[JobLineNo] [int] NOT NULL ,
[JobNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ResourceNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ResourceDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL )
JobNo JobDesc Status BillCustomer SalesPersonCode Maintenance J00001 Job 1 Order ABC Co JS 0 J00002 Job 2 Order Fish & Co RT 0 J00003 Job 3 Planning XYZ Co AB 1 J00004 Job 4 Quote EBS HC 0 JobNo JobLine ResourceNo ResourceDesc StartDate EndDate J00001 10000 V0001 Vessel1 1-Aug-05 3-Aug-05 J00001 20000 V0002 Vessel2 1-Aug-05 7-Aug-05 J00002 10000 V0003 Vessel3 8-Aug-05 10-Aug-05 J00003 10000 V0001 Vessel1 4-Aug-05 7-Aug-05 J00003 20000 V0002 Vessel2 9-Aug-05 12-Aug-05 J00004 10000 V0003 Vessel3 4-Aug-05 6-Aug-05 J00004 20000 V0003 Vessel3 10-Aug-05 12-Aug-05 So, basically, what I will get as input from the user would be:
- View Mode (Weekly or Monthly or Quarterly)
- Start Date (Example: 04/08/2005 dd/mm/yyyy)
Looking through the tables above, I will need to display the data in the format.
Vessel/Desc 1-Aug 2-Aug 3-Aug 4-Aug 5-Aug 6-Aug 7-Aug + Vessel1 - Vessel2 Orders Quote Maintenance - Vessel3 Orders Quote I would like to know if there are any other ways to write the sql statement other than creating temp tables.
Thanks!
-
Eunice, I cannot see how you create the display data from your testdata. Could you explain further?
-
Hello Jesper,
Simply put, User need to give 2 inputs:
1)View Mode (I'll start with Weekly)
2) StartDate as 04/08/2005 (dd/mm/yyyy)
I am hoping the stored procedure will create a temp table which will look like the display (see previous post). I am able to create the temp with the column names as 'Date' Thanks to David.
Stored Procedure will look into the JobLines table
JobNo JobLine ResourceNo ResourceDesc StartDate EndDate J00001 10000 V0001 Vessel1 1-Aug-05 3-Aug-05 J00003 10000 V0001 Vessel1 4-Aug-05 7-Aug-05 In this case, Vessel1 falls into the user input of 04/08/2005 (dd/mm/yyyy)
I will have the temp table populate with the following data:
Vessel/Desc 4-Aug 5-Aug 6-Aug 7-Aug 8-Aug 9-Aug 10-Aug - Vessel1 Null Null Null Null Null Null Null J00003 Orders Orders Orders Orders Null Null Null So that eventually, when it is displayed to the users, I will substitute the word 'Orders' with a Colour.
Vessel/Desc 4-Aug 5-Aug 6-Aug 7-Aug 8-Aug 9-Aug 10-Aug - Vessel1 J00003
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply