T-SQL /创建一个表，其列依赖于其他表的行

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.

我想创建一个具有动态列数的新表，具体取决于另一个表的行值。例如，我有一个表（table1）有2列名为'VALUE'和'ISACTIVE'（'ISACTIVE'列取值1如果我们需要将此值作为新表中的列考虑），我需要创建一个新表，其中包含：新表的列数（和列名）= table1的值，其中Isactive = 1。

3 个解决方案

#1

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

试试下面的内容。这假设所有列都是整数。如果它是varchar，我们可以相应地修改。我们需要改变现有的表并添加一个名为textval的列，默认为'0'

     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

现在根据isactive的值更新新列。如果它是5，则新列将具有最多col5所有beign整数并使用它来创建新表

  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

#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?

T-SQL如何在存储过程中动态创建表？

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)

#3

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

#1