Procedure to create a table with a variable number of columns

Question

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?

Thank you.

Answer 1

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

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.