Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71

    Unanswered: How To Write A Script To Create A Table (With Unfixed Field Names)?

    Hello All.

    You may find below script stupid to you. I am trying to create a table with week number that is not fixed, i.e. March 2006 may have 4 weeks and April 5 weeks based on our company calendar.

    And Field Names could be Grp, SubGrp, Week_10, Week_11, Week_12, Week_13 depending on the week being stored in TempWeekFile. We could start the month having only Week 10 and following week with Week 11 added into TempWeekFile.

    My below script shows error. I have following records in my TempWeekFile

    Week_No WeekCnt
    10 1
    11 2
    12 3
    13 4


    How to write a script that do the above? Has anyone done this using a better method? Please advise. Thank you.

    ---------------------------------------------------------

    if exists (select * from information_schema.tables where table_name='WeeklySalesToThird_Month')
    drop table WeeklySalesToThird_Month

    Declare @WeekCount int
    Declare @Cnt int
    Declare @WeekNo varchar(2)
    Select @WeekCount = count(*) from TempWeekFile
    Select @WeekCount
    Select @Cnt=1

    CREATE TABLE [dbo].[WeeklySalesToThird_Work_Month] (
    [Grp] [varchar] (30) NULL ,
    [SubGrp] [varchar] (30) NULL ,

    while @Cnt<=@WeekCount
    Begin
    select @WeekNo = Week_No from TempWeekFile
    where WeekCnt=@Cnt
    [Week_@WeekNo] [Money] NULL
    @Cnt=@Cnt+1
    End

    ) ON [PRIMARY]
    GO

    --------------------------------------------------------

  2. #2
    Join Date
    Jun 2003
    Posts
    269
    In such case u have to use dynamic sql.
    Code:
    if exists (select * from information_schema.tables where table_name='WeeklySalesToThird_Month')
    drop table WeeklySalesToThird_Month
    Declare @WeekCount int
    Declare @Cnt int
    Declare @WeekNo varchar(2)
    declare @sql varchar(8000)
    Select @WeekCount = count(*) from TempWeekFile
    Select @WeekCount
    Select @Cnt=1
    set @sql='CREATE TABLE [dbo].[WeeklySalesToThird_Work_Month] 
    (
    [Grp] [varchar] (30) NULL ,
    [SubGrp] [varchar] (30) NULL ,'
    while @Cnt<=@WeekCount
    Begin
     select @WeekNo = cast(Week_No as varchar) from TempWeekFile 
     where WeekCnt=@Cnt 
     set @sql=@sql+'[Week_'+@WeekNo+'] [Money] NULL,'
     set @Cnt=@Cnt+1
    End
    set @sql=substring(@sql,1,len(@sql)-1)
    set @sql=@sql+' ) ON [PRIMARY]'
    print @sql
    exec(@sql)
    Last edited by mallier; 03-28-06 at 09:49.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71

    Thank You

    Hi Millier.

    Thank you very much. Your script works PERFECT !!!!


    Best regards

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by limteckboon
    You may find below script stupid to you.
    Bingo. But not for the reasons you think.
    Why are you storing derived data in dedicated tables? This usually leads to trouble, and is an indication of poor application design.
    You should take mallier's logic and roll it into a stored procedure instead.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •