SQL Server Performance

Create a dynamic table with multiple columns.

Discussion in 'General Developer Questions' started by mic_lch83, Feb 2, 2007.

  1. mic_lch83 New Member

    Okay, here is goes. I just figured out how to 'select' two columns from the same one column with different requirements. Now, I would like to create dynamic columns and as many as I want.

    For example, if I type in
    "exec SP '5','1,2,34,52,656'"
    (5 for the number of variables and the second string is which variables)
    It will gives me columns that satisfied the requirements. The results of the table would be: (with the first columns being the common requirement)

    Variance PopTotal1 Poptotal2 Poptotal34 PopTotal52 PopTotal656
    1 x x x x x
    2 x x x x x

    Here is the code for creating two columns only.
    DECLARE @Var4 TABLE (RowNr INT IDENTITY(1,1), PopTotal INT)
    DECLARE @Var6 TABLE (RowNr INT IDENTITY(1,1), PopTotal INT)

    INSERT INTO @Var4 (PopTotal)
    SELECT PopTotal FROM table WHERE revid = 11 AND variance = 4 ORDER BY PopTotal

    INSERT INTO @Var6 (PopTotal)
    SELECT PopTotal FROM table WHERE revid = 11 AND variance = 6 ORDER BY PopTotal

    SELECT V4.PopTotal, V6.PopTotal
    FROM @Var4 v4
    FULL JOIN @Var6 v6 ON v4.RowNr = v6.RowNr
    ORDER BY ISNULL(v4.RowNr, v6.RowNr), ISNULL(v6.RowNr, v4.RowNr)

    How do I edit this to create as many column as I want?
  2. mic_lch83 New Member

    You can refer to topic with topicID = 19140. Thanks.
  3. Adriaan New Member

    The trick is to use a temp table, to which you will add a new column for each additional variance value. You can use dynamic SQL for this, so you can just keep a counter variable and concatenate that at the end of the column name.<br /><br />You then concatenate a single dynamic SQL statement that will do the following:<br />(1) declare a table variable, exactly like before,<br />(2) insert the PopTotals for the next matching variance value into the table variable,<br />(3) insert unmatched RowNr values from the table variable into the temp table,<br />(4) update the new column on the temp table with the PopTotal from the table variable, joining on the RowNr columns.<br /><br />You execute this long dynamic SQL, increase your counter, move to the next variance value from your list, and start again from the point where you added a new column to the temp table.<br /><br />Finally, do a<br /><br />SELECT * FROM #TmpTable<br /><br />to get all the results.<br /><br />Now that wasn't so terribly difficult, was it?[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]
  4. mic_lch83 New Member

    Thanks Adriaan. Let me figured out if I can do this. If not, let me figured out whether I have a choice to skip this.
  5. Adriaan New Member

    To recap ...

    Create temp table #MyTemp with a RowNr INT column and PopTotal_1 VARCHAR(100) column.

    Insert the results for the first variance parameter.

    Now you start a loop for all the other variance parameters ...
    Using a counter @Counter that starts at 2 ...

    -1- Use dynamic SQL for
    EXEC ('ALTER TABLE #MyTemp ADD PopTotal_' + LTRIM(CAST(@Counter) AS VARCHAR(100)) + ' VARCHAR(100)')
    This will add a column to #MyTemp.

    -2- Concatenate a single dynamic SQL statement that:
    (a) declares a table variable,
    (b) inserts the data for the variance parameter into the table variable (see earlier thread),
    (c) inserts missing RowNr values into the temp table:
    INSERT INTO #MyTemp (RowNr) SELECT RowNr FROM @var LEFT JOIN #MyTemp ON @Var.RowNr = #MyTemp.RowNr WHERE #MyTemp.RowNr IS NULL
    (d) updates the new column with the values from the table variable, based on the RowNr:
    UPDATE #MyTemp SET PopTotal_' + LTRIM(CAST(@Counter) AS VARCHAR(100)) + ' = @var.PopTotal
    FROM #MyTemp INNER JOIN @var ON #MyTemp.RowNr = @Var.RowNr
    ... execute the whole concatenated SQL statement
    ... and loop for the other variance parameters.

    You end up with a temp table with a column for each variance parameter - just use SELECT * to get them all.

Share This Page