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

     

  • 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)

     

  • 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/Desc1-Aug2-Aug3-Aug4-Aug5-Aug6-Aug7-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 )

    JobNoJobDescStatusBillCustomerSalesPersonCodeMaintenance
    J00001    Job 1Order               ABC Co    JS        0
    J00002    Job 2Order               Fish & Co RT        0
    J00003    Job 3Planning            XYZ Co    AB        1
    J00004    Job 4Quote               EBS       HC        0
    JobNoJobLineResourceNoResourceDescStartDateEndDate
    J00001    10000V0001     Vessel11-Aug-053-Aug-05
    J00001    20000V0002     Vessel21-Aug-057-Aug-05
    J00002    10000V0003     Vessel38-Aug-0510-Aug-05
    J00003    10000V0001     Vessel14-Aug-057-Aug-05
    J00003    20000V0002     Vessel29-Aug-0512-Aug-05
    J00004    10000V0003     Vessel34-Aug-056-Aug-05
    J00004    20000V0003     Vessel310-Aug-0512-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/Desc1-Aug2-Aug3-Aug4-Aug5-Aug6-Aug7-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

    JobNoJobLineResourceNoResourceDescStartDateEndDate
    J00001    10000V0001     Vessel11-Aug-053-Aug-05
    J00003    10000V0001     Vessel14-Aug-057-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/Desc4-Aug5-Aug6-Aug7-Aug8-Aug9-Aug10-Aug
    -Vessel1 Null Null Null Null Null Null Null
     J00003OrdersOrdersOrdersOrders Null Null Null

    So that eventually, when it is displayed to the users, I will substitute the word 'Orders' with a Colour.

     Vessel/Desc4-Aug5-Aug6-Aug7-Aug8-Aug9-Aug10-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