创建具有可变列数的表的过程 [英] Procedure to create a table with a variable number of columns

问题描述

我正在尝试创建一个存储过程，该过程将创建一个临时表 A，其列将取决于另一个表 B 的行数.这样做的背景是我允许用户创建自己的'groups'，有关组的信息将存储在表 B 中.表 A 的创建将查看表 B 中的行数，并为表 B 中的每个组创建一列(使用组名在表 B 作为表 A 中的列名).我无法反转轴(轴?)，因为行将是我系统中每个用户的条目，这也是可变的.

I'm trying to make a stored procedure that will create a temporary table A whose columns would be dependent on the number of rows of another table, B. The background for this is that I'm allowing users to create their own 'groups', and information about the groups would be stored in table B. The creation of table A would look at the number of rows in table B, and create a column for each group in table B (using the name of the group in table B as the column name in table A). I am unable to invert the axes (axises?) because the rows would be an entry for each user in my system, which would also be variable.

总而言之:我将如何创建一个过程来创建具有可变列数的临时表?

So in summary: How would I create a procedure to create a temporary table with a variable number of columns?

谢谢.

推荐答案

DECLARE @sSQL varchar(max),
  @ColumnName CHAR(128)

DECLARE TableCursor CURSOR FOR
  SELECT ColumnName FROM GroupTable

SET @sSQL = 'CREATE TABLE ##NewTempTable ('

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN 

SET @sSQL = @sSQL + RTRIM(@ColumnName) + ' CHAR(10) ,'

FETCH NEXT FROM TableCursor INTO @ColumnName

END

CLOSE TableCursor

DEALLOCATE TableCursor

SET @sSQL = @sSQL + ')'

EXEC (@sSQL)

SELECT * FROM ##NewTempTable

我希望这会有所帮助.在 DECLARE CURSOR 中，您需要将ColumnName"和TableName"更改为您要查询的实际列/表.

I hope this helps. In the DECLARE CURSOR, you will need to change the "ColumnName" and "TableName" to your actual column/table that you are querying.

这篇关于创建具有可变列数的表的过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！