Dynamic Table Creation

• rdg

  Mr or Mrs. 500

  Points: 516

  More actions

  December 3, 2003 at 7:26 am

  #101784

  In the creation of a report, I need to set up a temporary table. The problem is, I don't know how many columns I need.

  For example, I want to list all pupils from one class in a school and show their main teacher. However, I also want to list any additional teacher they may have with in the same record.

  Pupil Name, Age, Main Teacher, 2nd Teacher, 3rd Teacher, etc.

  Pupil 1, 6, Teacher A, , ,

  Pupil 2, 7, Teacher A, Teacher C, ,

  Pupil 3, 6, Teacher A, Teacher B, Teacher C,

  Pupil 4, 6, Teacher A, Teacher B, ,

  Before I begin, I guess I would need to count how many different teachers there are, but how do I then create a table with the correct number of columns dynamically?

• Greg Larsen

  SSC-Insane

  Points: 20966

  More actions

  December 3, 2003 at 7:48 am

  #484447

  If you know how many columns you want to create, you could do something like this to dynamically build the create table statement.

  declare @colcnt int

  declare @cmd varchar(1000)

  declare @i int

  -- this variable is set to the number of columns you want to create

  set @colcnt = 10

  set @i = 0

  while @i < @colcnt

  begin

  set @i = @i + 1

  if len(@cmd) > 0 set @cmd = @cmd + ','

  set @cmd = isnull(@cmd,'') + 'col_' + rtrim(cast(@i as char(2))) +

  ' varchar(50)'

  end

  set @cmd = 'create table ##dynamic (' + @cmd + ')'

  exec (@cmd)

  select * from ##dynamic

  drop table ##dynamic

  Gregory A. Larsen, DBA

  Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:www.sqlservercentral.com/bestof/purchase.asp

  Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

  Gregory A. Larsen, MVP

• rdg

  Mr or Mrs. 500

  Points: 516

  More actions

  December 3, 2003 at 7:58 am

  #484448

  Thanks Greg

  I modified this a little to parse in the rowcount from the previous table which enabled me to create how ever many columns were needed.

• noeld

  SSC Guru

  Points: 96590

  More actions

  December 3, 2003 at 8:22 am

  #484452

  I would not use a temp table if the final result is

  Pupil 1, 6, Teacher A, Teacher B , ...

  You Can construct the Teacher A, B, .. into One Column and pass that to the client for furhter processing

  
  * Noel