Add coloumns dynamically in a table

[shashankpatwardhan]

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

[debasisdas]

What about the column datatype ? From where will you get that ?

[madankarmukta]

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

[madankarmukta]

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

Regards,
Mukta

[ck9663]

Pivot your table. If necessary, build your query dynamically.

-- CK