Create a Dynamic Table using a cursor

  • adi.cohen

    Old Hand

    Points: 371

    Hi,

    I have a table that contains one column with nvarchar. Let's say this is the table:

    Reasons_T -----------

    | Reason |

    -----------

    | xxx |

    | yyy |

    | zzz |

    now I want to dynamically create a new table that has 1 column for each row in the previous table. meaning:

    Summary_T

    -----------

    | xxx | yyy | zzz |

    -----------------

    | | | |

    | | | |

    | | | |

    I used the following cursor but I'm getting a syntax error (for the bolded row below):

    --create the Table

    CREATE TABLE Summary_T

    --Add the columns

    DECLARE @Reason nvarchar(10)

    DECLARE cr cursor local for

    SELECT DISTINCT [Reason]

    FROM Reason_T

    open cr

    fetch next from cr into @Reason

    while @@fetch_status=0

    begin

    --**********************************************************

    ALTER TABLE Summary_T

    ADD @Reason int

    **********************************************************

    fetch next from cr into @Reason

    end

    close cr

    deallocate cr

    will love for your inputs of what am I doing wrong...

    thanks!:)

  • Gail Shaw

    SSC Guru

    Points: 1004446

    You can't use variables within an alter table statement. You're going to need dynamic SQL for this.

    DECLARE @Reason nvarchar(10), @sSQL NVARCHAR(500)

    DECLARE cr cursor local FAST_FORWARD for

    SELECT DISTINCT [Reason]

    FROM Reason_T

    open cr

    fetch next from cr into @Reason

    while @@fetch_status=0

    begin

    SET @sSQL = 'ALTER TABLE Summary_T ADD ' + @Reason + ' int'

    EXEC @sSQL

    fetch next from cr into @Reason

    end

    close cr

    deallocate cr

    I'm curious. What are you trying to achieve here?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • adi.cohen

    Old Hand

    Points: 371

    Hi,

    First - thanks alot for this quick response!

    The reasons table is dynamic (we add reasons once in a while) and I'm making a summary of this into a report. the report will count how many times each Reason was selected for a certain Employee. (the Summary table contains also Employee ID column)

    I got this error:

    'ALTER TABLE Summary_T ADD [Dep - HC request] int' is not a valid identifier

    Do you know what might be the problem?

    thanks!:)

  • adi.cohen

    Old Hand

    Points: 371

    I think the [ ] were the problem... when I removed it from the reasons table I got the following error for each of the rows in the reasons table....:

    Could not find stored procedure 'ALTER TABLE ResSum ADD Dep-HCrequest int'.

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Sorry, my mistake. The exec line should have been.

    EXEC (@sSQL)

    Note the brackets.

    The reasons table is dynamic (we add reasons once in a while) and I'm making a summary of this into a report. the report will count how many times each Reason was selected for a certain Employee. (the Summary table contains also Employee ID column)

    Hmm. Take a look at the PIVOT keyword in SQL 2005. It's possible you may be able to use that instead of building a dynamic table.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Timothy J Hartford

    SSCarpal Tunnel

    Points: 4214

    I think GilaMonster is right in that this additional table seems unnecessary. Why not create a view with the summarized fields (using a pivot)?

  • lpfaff

    Grasshopper

    Points: 15

    I have a requirement to build a sql statement that will dynamic pass the tablename and 2 columns names. I have a result table that that has this information. the table and column name along with the assetid I should be able to iterate through my table and return a field value for each row item, which I will need to join to my results table later. I am not familiar with cursor and believe I will need to go this route to retrieve my results. The three parameters I have to get my results are
    TableName - dynamic
    FieldName (or columnname in table) - dynamic
    AssetId (same columnname in all tables)
    any help on how too start would be appreciated
    thanks

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply