Create a Dynamic Table using a cursor

Question

Post reply

Create a Dynamic Table using a cursor

adi.cohen

Old Hand

Points: 371
More actions
July 7, 2008 at 1:00 am

#381513

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!:)

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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: 1004504 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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