Variable column names in a table and or view

• Andy Howell

  SSC Veteran

  Points: 247

  More actions

  September 30, 2005 at 9:35 am

  #168539

  Hi all,

  I am trying to create a table with column names that equal variables that change each month.  The table will be dropped after use and is created by the SP that works our the variables.

  Currently out put is:

  User      P1      P2     P3  

  Andy     10      10      50

  I have set @M1 = 200506, @M2 = 200507 and @M3 = 200508 and want to replace the P1,P2 and P3 with the variables stored in @M1 etc to give me.

  User      200506   200507   200508 

  Andy     10            10          50

   

  Any ideas would be gratefully received,

  Thanks,  Andy 

• October 3, 2005 at 8:00 am

  This was removed by the editor as SPAM

• atra

  SSC Veteran

  Points: 256

  More actions

  October 5, 2005 at 2:08 am

  #595077

  I'm not sure if this is what are you trying to do but i hope it will help you

  declare @M1 char(6)

  declare @M2 char(6)

  declare @M3 char(6)

  declare @tmp_sql char(1000)

  select @M1 = '200506'

  select @M2 = '200507'

  select @M3 = '200508'

  select @tmp_sql = ' create table tmp_table ( users char(20) , ' + '"' + @M1 + '"' + ' int , ' + '"' +

       @M2 + '"' + ' int, ' + '"' + @M3 + '"' +' int)'

  exec(@tmp_sql)

  drop table tmp_table

  you must have QUOTED_IDENTIFIER option set to ON

• Andy Howell

  SSC Veteran

  Points: 247

  More actions

  October 5, 2005 at 3:39 am

  #595091

  Many thanks for that, it was exactly what I was looking for

   

  Cheers

  Andy