如何在sqlserver2008中动态创建列 [英] How to create columns dynamically in sqlserver2008

问题描述

大家好，



如何在sqlserver.i中动态创建列创建了一个包含A，B和C列的表。



a b c

1 2 3

2 4 5

3 5 6





i从excel读取数据并插入表格，假设我从excel获得三列，那么没有问题。如果我得到超过三列，我必须找出新栏目

并根据它改变表格，并需要插入如下所述的数据。



a b c D E

1 2 3

2 4 5

3 5 6

1 2 3 5 6

2 4 5 8 9

3 5 6 7 4





如果有人知道，请帮助我如何做同样的事情。







感谢你，

解决方案

我建​​议你不要在运行时向表中添加列。相反，你有两种可能性。



1.您可以预先确定列数。遍历数据并查看所需的列数。然后在添加数据时，将插入语句中的空值保留为 null 。



2. Re - 模拟数据库结构。不是将数据作为DB列存储在Excel列中，而是将数据转置为行。这意味着你会有类似（伪定义）的表：

- rownumber

- columnnname

- 值



所以你的例子中的行可能是：

 rownumber columnname value 
 --------- ---------- ----- 
 1 a 1 
 1 b 2 
 1 c 3 
 2 a 2 
 2 b 4 
 ... 
 4 a 1 
 4 b 2 
 4 c 3 
 4 d 5 
 4 e 6 
 ... 

Hi Everyone,

How to create columns dynamically in sqlserver.i have created a table with the columns A,B and C.

a b c
1 2 3
2 4 5
3 5 6


i am reading data from excel and inserting into the table, let suppose i am getting three columns from excel then there is no issue. if am getting more than three columns i have to find out the new column
and alter the table according to it and need to insert the data like below mentioned example.

a b c D E
1 2 3
2 4 5
3 5 6
1 2 3 5 6
2 4 5 8 9
3 5 6 7 4


if anyone knows,kindly help me how to do the same.



thanking you,

解决方案

I would suggest that you do not add columns to the table at runtime. Instead you have two possibilities.

1. You can decide the amount of columns beforehand. Loop through the data and see how many columns you need. Then when adding the data, leave the empty values in in the insert statement as null.

2. Re-model you database structure. Instead of storing the data in Excel columns as DB columns, transpose the data to be rows. This would mean that you would have table something like (pseudo definition):
- rownumber
- columnnname
- value

So the rows from your example could be:

rownumber columnname value
--------- ---------- -----
1         a          1
1         b          2
1         c          3
2         a          2
2         b          4
...
4         a          1
4         b          2
4         c          3
4         d          5
4         e          6
...

这篇关于如何在sqlserver2008中动态创建列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！