动态添加列名并将其值添加为行sql server

I have a table table1 with column name a, b, c, d, e, f. Now the task is to get the value of each column which will definitely be a single row value and insert that into other table2 - columns(x, y, z) . So my query would be like :

我有一个表table1，列名为a，b，c，d，e，f。现在的任务是获取每列的值，它肯定是单行值并将其插入到其他table2 - 列（x，y，z）中。所以我的查询将是：

 insert into table2 (x, y, z)
select a, '', '' from table1
union all
select b, '', '' from table1
union all
select c, '', '' from table1
union all
select d, '', '' from table1
union all
select e, '', '' from table1
.
.
.
union all
select f, '', '' from table1

Now if a new column add in table1 then again I have to add a select statement in this. Just want to avoid this how can I write a dynamic query which automatically consider all the columns and make it shorter.

现在，如果在table1中添加了一个新列，那么我必须再添加一个select语句。只是想避免这种情况如何编写动态查询，自动考虑所有列并使其更短。

4 个解决方案

#1

Seems like your looking for a Dynamic EAV Structure (Entity Attribute Value). Now the cool part is the @YourTable could be any query

看起来像是在寻找动态EAV结构（实体属性值）。现在很酷的部分是@YourTable可以是任何查询

Declare @YourTable table (ID int,Col1 varchar(25),Col2 varchar(25),Col3 varchar(25))
Insert Into @YourTable values
 (1,'a','z','k')
,(2,'g','b','p')
,(3,'k','d','a')


Select A.ID
      ,C.*
 From  @YourTable A
 Cross Apply (Select XMLData=cast((Select A.* for XML Raw) as xml)) B
 Cross Apply (
                Select Attribute = attr.value('local-name(.)','varchar(100)')
                      ,Value     = attr.value('.','varchar(max)')              -- change datatype if necessary
                 From  B.XMLData.nodes('/row') as A(r)
                 Cross Apply A.r.nodes('./@*') AS B(attr)
                 Where attr.value('local-name(.)','varchar(100)') not in ('ID','OtherFieldsToExclude')  -- Field Names case sensitive
             ) C

Returns

ID      Attribute   Value
1       Col1        a
1       Col2        z
1       Col3        k
2       Col1        g
2       Col2        b
2       Col3        p
3       Col1        k
3       Col2        d
3       Col3        a

#2

A simpler way to do this uses cross apply:

更简单的方法是使用交叉应用：

insert into table2 (x, y, z)
    select v.x, '', ''
    from table1 t1 cross apply
         (values (t1.a), (t1.b), (t1.c), (t1.d), (t1.e), (t1.f)
         ) v(x);

If you want to insert new values when new columns are added to the table, then you would want a DDL and probably a DML trigger. DML triggers are the "standard" triggers.

如果要在将新列添加到表中时插入新值，则需要DDL并且可能需要DML触发器。 DML触发器是“标准”触发器。

You can read about DDL triggers in the documentation.

您可以在文档中阅读有关DDL触发器的信息。

That said, I am highly suspicious of database systems that encourage new columns and new tables to be added. There is probably a better way to design the application, for instance, using an EAV data model that provides greater flexibility with attributes.

也就是说，我非常怀疑鼓励添加新列和新表的数据库系统。可能有更好的方法来设计应用程序，例如，使用EAV数据模型，该模型为属性提供更大的灵活性。

#3

try this

尝试这个

insert into table2 
select Tmp.id, tb1.* from table1 tb1,
((SELECT B.id FROM (SELECT [value] = CONVERT(XML ,'<v>' + REPLACE('a,b,c,d,e,f' , ',' , '</v><v>')+ '</v>')) A     
OUTER APPLY 
(SELECT  id = N.v.value('.' , 'varchar(100)') FROM A.[value].nodes('/v') N ( v )) B)) Tmp

#4

This, if I am reading it correctly, looks like a perfect time to use PIVOT.

如果我正确地阅读它，这看起来是使用PIVOT的最佳时机。

#1