0

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.

user856358
  • 573
  • 1
  • 7
  • 18

2 Answers2

3
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.

Sumurai8
  • 20,333
  • 11
  • 66
  • 100
FAA
  • 179
  • 11
  • Please do not add signatures to your answer. – Sumurai8 Aug 29 '13 at 21:38
  • Ah brilliant. This is what I was looking for: the ability to run arbitrary code as a string. Sorry for the late reply. The only thing I have to add is a couple validations/replace statements for the @Column variable to ensure that the data that I am pulling is appropriate for a column name (eg replacing blank spaces with underscores) – user856358 Sep 04 '13 at 15:40
0

You should not do this - instead, make the group name a column in table A as well.

We Are All Monica
  • 13,000
  • 8
  • 46
  • 72
  • To be clear,your solution would be to pool all the group names in table B to one column in table A? There's no way at all in SQL to create an individual column for each group in table B in table A? – user856358 Aug 29 '13 at 18:41
  • Yes, that's right. I'm sure there's a way to do it, but it goes against the nature of SQL to have variable table definitions like that, because it makes it hard or impossible to write your queries without specifying the column names dynamically. Also imagine how difficult it would be to write queries that compute metrics over different categories of groups, or join together different tables on group name. – We Are All Monica Aug 29 '13 at 18:54
  • 1
    It is also extremely cumbersome to build a dynamic #temp table and also refer to it later since, once you execute the dynamic code in which it is created, it no longer exists. Therefore all the subsequent code would have to be inside the same block of dynamic SQL. Yuck. Are you sure you need a temporary table at all? Maybe instead of asking how to accomplish a dynamically created temp table you ask how to solve the actual problem you're trying to solve? – Aaron Bertrand Aug 29 '13 at 19:05