Add values to a Temp Table in MS SQL

I create a temp table in SQL.  The first columns are always the the same columns and the last two are always the same columns.  I create all the other columns dynamically and there could be between 5 - 20 of them.

I will have a variable set to something like this - @var varchar(max) = '110, 120, 130, 140, 150, 155, 156'
This numbers would be the dynamic columns in my temp table.

I need to loop through the variable and if 110 is there place a value in that column, if it is missing place a zero in that column.

Then I need to do this loop again for a new person.

Thanks
triplebd69Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
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;
0
triplebd69Author Commented:
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
0
Kyle AbrahamsSenior .Net DeveloperCommented:
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?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

triplebd69Author Commented:
All columns once created with be included, but each person won't have every column.
Here is the pic.
grid.PNG
0
MlandaTCommented:
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)

Open 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 )

Open 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.
0
triplebd69Author Commented:
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
0
MlandaTCommented:
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.
0
triplebd69Author Commented:
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
0
MlandaTCommented:
DECLARE @flds varchar(max), @sql varchar(max)

--create field names dynamically
SELECT @flds = ISNULL(@flds + ', ', '') + QUOTENAME(JOB)
FROM (SELECT DISTINCT JOB FROM #temp WHERE parent_id = 2) Fields
ORDER BY JOB ASC
--PRINT  @flds

--create PIVOT
SELECT @sql = 'SELECT [ID], [Project_ID], 
' + @flds + ', 
' + REPLACE(@flds, ', ', ' + ') + ' AS Worked
FROM  (select id, project_id, job from #temp) data
PIVOT (
   COUNT([JOB])
   FOR [JOB] IN (' + @flds + ')
) p'

PRINT @sql
EXEC ( @sql )

Open in new window

You can filter line 13 for WHERE parent_id = 2 if you like.
Comment out (and run up to) line 7 to see how it generates the list of column names dynamically
If you wrote the PIVOT command by hand, it would look like what line 19 prints to the Messages window
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
triplebd69Author Commented:
Works great thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.