triplebd69
asked on
SQL Temp Tables Dynamic Columns
I am creating a temp table with one column.
create table #temp(JobID int);
Then I dynamically add more columns to the temp table.
SET @script = 'ALTER TABLE #temp ADD '
Select @script = @script + QUOTENAME(job) + ' INT DEFAULT 0 WITH VALUES,'
from Jobs
Now my table looks like this.
JobID Station1 Station2 Station3 Station4
3000 0 0 0 0
3002 0 0 0 0
3152 0 0 0 0
4125 0 0 0 0
Now I need to Update the dynamic columns (Station1 Station2 Station3 Station4)
for each JobID to get results like this.
JobID Station1 Station2 Station3 Station4
3000 52 48 55 60
3002 62 59 63 55
3152 79 71 58 60
4125 21 28 34 19
Any help would be helpful.
Thanks
create table #temp(JobID int);
Then I dynamically add more columns to the temp table.
SET @script = 'ALTER TABLE #temp ADD '
Select @script = @script + QUOTENAME(job) + ' INT DEFAULT 0 WITH VALUES,'
from Jobs
Now my table looks like this.
JobID Station1 Station2 Station3 Station4
3000 0 0 0 0
3002 0 0 0 0
3152 0 0 0 0
4125 0 0 0 0
Now I need to Update the dynamic columns (Station1 Station2 Station3 Station4)
for each JobID to get results like this.
JobID Station1 Station2 Station3 Station4
3000 52 48 55 60
3002 62 59 63 55
3152 79 71 58 60
4125 21 28 34 19
Any help would be helpful.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.