Add values to a Temp Table in MS SQL

so you create a #temp table in SQL
then you ALTER #temp table and add more columns - you know all their names
then you need to
DECLARE @value sysname;
SET @value = 'whatever';
UPDATE #temp SET ColX = CASE WHEN @var=110 THEN @value ELSE 0 END;

I have attached a pic of the grid to help with explanation.

Emp ID, Team Member, Worked and Total are always the same.  The yellow highlighted ones will change.  SO for example AL will have something like this for @var = '110, 120, 135', then Brian would have @var = '115, 125, 130' and so on.  I need to take the values and insert them in the correct row and column and do it for all team members.

Thanks

Just a note the pic isn't attached.

Will there be different columns for different people?

eg:

can Joe have 115, 125, 130, 215?

Do the values have mappings or do they just insert in the middle columns left to right?

All columns once created with be included, but each person won't have every column.
Here is the pic.
grid.PNG

This sounds like something that really should be built with the PIVOT command. What does the source of this data look like?  It is unclear how the column "Total" is calculated so I ommit it in the example below. SQL does badly with loops, and why use loops where you can use set based operations.

Create sample table

CREATE TABLE Table1
    ([Emp ID] int, [Team Member] varchar(5), [Team Name] int)
;
    
INSERT INTO Table1
    ([Emp ID], [Team Member], [Team Name])
VALUES
    (1, 'Al', 110),
    (1, 'Al', 110),
    (1, 'Al', 120),
    (2, 'Brian', 115),
    (2, 'Brian', 125),
    (2, 'Brian', 130),
    (5, 'Eli', 140)

Select allOpen in new window

Generate the SQL to PIVOT that data:

DECLARE @flds varchar(max), @sql varchar(max)

--create field names dynamically
SELECT @flds = ISNULL(@flds + ', ', '') + QUOTENAME([Team Name])
FROM (SELECT DISTINCT [Team Name] FROM Table1) Fields
ORDER BY [Team Name] ASC
PRINT  @flds

--create PIVOT
SELECT @sql = 'SELECT [Emp ID], [Team Member], 
' + @flds + ', 
' + REPLACE(@flds, ', ', ' + ') + ' AS Worked
FROM Table1
PIVOT (
   COUNT([Team Name])
   FOR [Team Name] IN (' + @flds + ')
) p'

PRINT @sql
EXEC ( @sql )

Select allOpen in new window

Fiddle: http://sqlfiddle.com/#!3/a3ba1/5

IF you really want to create your temp table, rather focus your time on building a temp table which looks like Table1 in the example above. Then run the PIVOT command on this temp table.

In the sql.txt attachment is the code I use to create the dynamic columns.  The columns.png shows the results.

So looking at the grid.png the columns would have Al and some numbers, then Brian and some numbers, etc.

Without knowing the column names how can I select them for a pivot?
sql.txt
columns.PNG
grid.PNG

Without knowing the column names how can I select them for a pivot?
If you follow the code I posted, there is a section where the comment reads "create field names dynamically". The key issue is to take a step backwards (from your current approach or what you have discussed here so far) and decide whether your data source allows you use the PIVOT. By data source, I'm talking about your table Jobs. What does that look like? I suppose this table would be the equivalent of my Table1 (in the posted sample code). Can you post a sample of data from that table? Or it's structure with a few dummy records? Without that... this is going to take longer than it should.

Here is the table structure and data.  In essence I need everything in the job column that has a parent_id of 2 are the the dynamic columns.

Thanks
Table-and-Data.sql
Temp-Table.PNG

Works great thanks.