By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,363 Members | 2,417 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,363 IT Pros & Developers. It's quick & easy.

Add coloumns dynamically in a table

P: 1
I have a table tbl_tmp1

There are two fields

EmpId EmpName

I have another table tbl_Heads in this table ther are total 8 records

I want to add number of coloumns equal to the row count i.e 8 of table tbl_Heads in tbl_tmp1. The newly added coloumns name should be the same of the name of the value of the HeadName field in table tbl_Heads

How this is possible
Apr 11 '08 #1
Share this Question
Share on Google+
4 Replies

Expert 5K+
P: 8,127
What about the column datatype ? From where will you get that ?
Apr 11 '08 #2

P: 293
The solution to the problem stated by you is possible with executable query

as exec 'Alter table tablename add ' + cloumname + ' datatype'
in the above columnname can be accessed by using the cursor over your table with 8 rows i.e. tbl_header.

the cursor defination will be
declare cur cursor as select headername from tbl_header

As u will iterate through the cursor the next headername will be available to the above mentioned executable query.

Thus the Cloumns will be added dynamically.
Apr 11 '08 #3

P: 293
sqlvarient is datatype which is use to hold the data of any type .. you can use it.
Or else you can use IsNumeric to check for the numeric value.If it is non numeric the data might be string .This is the way how you can decide datatype of the values in the table.

Apr 11 '08 #4

Expert 2.5K+
P: 2,878
Pivot your table. If necessary, build your query dynamically.

-- CK
Apr 11 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.