Adding Dynamic column to a dynamic table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Adding Dynamic column to a dynamic table

Author

Topic

sushil.arthi
Starting Member

1 Post

Posted - 2005-03-01 : 01:13:29

Hi,

I am not able to add a dynamic column to a dynamic table in SQL Server. This scenario came when i need to retrieve a set of employees who are working in an organization with the specific effort they have put per each day with which, i will get the list of employees with the day-wise effort of each employee.

I tried in this way...

Declare @dtFromDate as Datetime
Declare @dtToDate as Datetime
Set @dtFromDate = '2/2/2005'
Set @dtToDate = '3/3/2005'

Declare @date_Table Table (DDate Varchar(20))
while @dtFromDate <= @dtToDate
Begin
Insert into @date_Table values (@dtFromDate)
Set @dtFromDate = DateAdd(Day,1,@dtFromDate)
End

Declare @Result_Table Table (EmpName Varchar(60))
Declare @col_Date as Varchar(20)
Declare @Date_Cursor Cursor For
Select DDate From @date_Table

Open @Date_Cursor
Fetch Next From @Date_Cursor into @col_Date
while @@FETCH_STATUS = 0
Begin
Alter table @Result_Table Add @col_Date Varchar(20) Null
Fetch Next From @Date_Cursor into @col_Date
End
Close @Date_Cursor
Deallocate @Date_Cursor

Plz help me out...

Thanks in advance,
Regards,
Sushil

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-03-01 : 07:12:55

It looks like you're trying to do a cross tab, try this instead:

http://www.sqlteam.com/item.asp?ItemID=2955

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources