question

I'm going to post all the edits I've made on StackOverflow trying to figure this one out. >Additionally, I have to figure out how to put this idea into a very large (already written but not by me query) that is getting all the stuff that fills this table from multiple databases. EDIT: | Scenario_Name | Measure | Value | |---------------|-------------|-------| | Scenario1 | Weekends | 10 | | Scenario1 | Weekdays | 25 | | Scenario1 | TotalPeople | 35 | | Scenario2 | Weekends | 12 | | Scenario2 | Weekdays | 30 | | Scenario2 | TotalPeople | 42 | | Scenario3 | Weekends | 5 | | Scenario3 | Weekdays | 15 | | Scenario3 | TotalPeople | 20 | There are more entries for "Measure" and the Scenario files will repeat for each version. However, the point is that I want the entry under Measure "TotalPeople" to be the result of a query that pulls the float from Value column and sums both Weekends and Weekdays and puts that value into TotalPeople EDIT: I've continued to figure some stuff out. This is an example of that query I mentioned in my final statment before my first EDIT. SELECT (SELECT attributedata FROM [' +@dbname+ '].table1 WHERE AttributeName = ''Scenario Name'') AS Scenario_Name, (SELECT attributedata FROM [' +@dbname+ '].table1 where AttributeName = ''Version'') AS Version, CAST(COUNT(*) AS float)/MAX(repnum) AS value, finalDisposition AS Measure, GETDATE() AS DateRun, (SELECT ' + CAST(@testid as CHAR) +') AS TestNum FROM [' +@dbname+ '].table2 GROUP BY FinalDisposition I think I simply need to use a UNION to add another one but change what computes as Value and make TotalPeople as Measure So using this, anyone have any ideas on how they would do that? I know that everything except the query that calls Measure and Value need to stay the same. Its also worth noting that the entries for the "Measure" column are being pulled from entries in a different database. Databases that are assosited with each Scenario_Name and pulled using the @dbname variable. So how would I use that format to add in a new "Measure" entry that is called TotalPeople? SELECT CAST(COUNT(id) AS float) AS Value, FinalDisposition AS Measure FROM [' +@dbname '].table2 WHERE FinalDisposition = 'Weekdays' OR FinalDisposition = 'Weekends' GROUP BY FinalDisposition the "id" that is being counted is id for people in the scenario that comes from other databases just like FinalDisposition is a column name from other tables in the databases. This will pull the values that I need but now how can I sum them and have the sum equal a new Measure entry?

Not sure about the values of Measures coming from 'another database', but here is a simplified example of making updates based on the logic you stated declare @Simulation table (Scenario_Name varchar(50), Measure varchar(50), Value int) insert into @Simulation select 'Scenario1','Weekends',10 insert into @Simulation select 'Scenario1','Weekdays',25 insert into @Simulation select 'Scenario1','TotalPeople',0 insert into @Simulation select 'Scenario2','Weekends',12 insert into @Simulation select 'Scenario2','Weekdays',30 insert into @Simulation select 'Scenario2','TotalPeople',0 insert into @Simulation select 'Scenario3','Weekends',5 insert into @Simulation select 'Scenario3','Weekdays',15 insert into @Simulation select 'Scenario3','TotalPeople',0 update sim set Value = SummedValues.TotalPeople from @Simulation sim join ( select Scenario_Name, sum( case when Measure = 'Weekends' then Value else 0 end + case when Measure = 'Weekdays' then Value else 0 end ) TotalPeople from @Simulation group by Scenario_Name ) SummedValues on SummedValues.Scenario_Name = sim.Scenario_Name where sim.Measure = 'TotalPeople' select * from @Simulation