T-SQL / Create a table that his columns depend on rows of an other table

Question

i want to create a new table that has dynamic number of columns depending on the row values of an other table. For example i have a table (table1) that has 2 columns named 'VALUE' and 'ISACTIVE' ('ISACTIVE' column takes the value 1 if we need to take into account this value as a column in the new table) and i need to create a new table that has: number of columns (and column name) of new table = the values of table1 where Isactive = 1.

Answer 1

SELECT [attributeName] INTO [DatabaseName].[dbo].[NewTableName] 
FROM [DatabaseName].[dbo].[FromTableName] WHERE ISACTIVE=1

Answer 2

My first thought was to create it dynamically in a procedure based on your conditions. Read this question and answers , it will help.

T-SQL How to create tables dynamically in stored procedures?

Raw example

DECLARE @SQLString NVARCHAR(MAX)

SET @SQLString = N'CREATE TABLE <table_name> ('
        -- Conditons here
        SET @SQLString = @SQLString + '<column_name> <type>'

        -- End of conditions

    SET @SQLString = @SQLString + ')'

    EXEC (@SQLString)

Answer 3

Try out the below.This is assuming all the columns to be integer .we can modify accordingly if it is varchar .We need to alter the existing table and add a column called textval which defaults to '0' here

     drop table test
     create table test
    (
      value integer,
      isactive integer
    );
  alter table test add  textval nvarchar(max) default '0'
  insert into test (value,isactive) values (123,5);

   select * from test;

Now update the new columns based on the value of isactive .if it is 5 the new column will have upto col5 all beign integer and use this to create a new table

  begin 

   declare @i integer;
   set @i=1
   declare @isactive integer;
   declare @stmt nvarchar(max);
   declare @stmt2 nvarchar(max);
   declare @testval nvarchar(max);

   set @isactive= (select isactive from test)
   while (@i <=@isactive)
     begin
         declare @textval nvarchar(max);
         set @textval = (select textval from test)
        set @stmt= 'update test set textval = '''+ @textval +'col' +cast(@i 
        as varchar(100)) + ' ' + 'integer,'''

      execute sp_executesql @statement=@stmt
     set @i=@i+1;
    end 
  set @testval=(select left(replace(textval,'0col1','col1'),len(textval)-2) 
               from test)

   set @stmt2 ='create table tab1 ( ' + @testval + ' )';
   execute sp_executesql @statement=@stmt2;

  end