Myujth

I have 12 Variables and all of them have the same SELECT Statement exept for 1 Param. I somehow cant figure out how to so it dynamically.

Here is an example of what I have:

// this here should be dynamic
SET @Var1 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '1')
SET @Var2 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '2')
SET @Var3 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '3')
SET @Var4 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '4')
SET @Var5 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '5')
SET @Var6 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '6')
SET @Var7 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '7')
SET @Var9 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '8')
SET @Var9 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '9')
SET @Var10 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '10')
SET @Var11 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '11')
SET @Var12 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '12') 

INSERT INTO VarTable (Var1, Var2, Var3, Var4, Var5, Var6, Var7, Var8, Var9, Var10, Var11, Var12)
 VALUES (@Var1, @Var2, @Var3, @Var4, @Var5, @Var6, @Var7, @Var8, @Var9, @Var10, @Var11, @Var12)

The Table Cols are restricted (no permission to change the table) so I need to assign it like this.

This looks like it should be a PIVOT:

INSERT INTO VarTable (Var1, Var2, Var3, Var4, Var5, Var6, Var7, Var8, Var9, Var10, Var11, Var12)
SELECT [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
FROM (select MonthCol,COUNT(*) Cnt from #TempTable group by MonthCol) t
PIVOT (SUM(Cnt) FOR MonthCol IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) u

I believe we have to "pre-aggregate" the data in a subquery because COUNT(*) isn't allowed in the PIVOT clause¹. Note that each MonthCol will have a single Cnt value so we could use any aggregate in the PIVOT that returns a single input unaltered - SUM, MIN or MAX. I arbitrarily picked SUM above.

¹You have to supply specific column(s) to the aggregate.

I think you don't need to use variable here :

INSERT INTO VarTable (Var1, Var2, Var3, Var4, Var5, Var6, Var7, Var8, Var9, Var10, Var11, Var12)
 SELECT SUM(CASE WHEN MonthCol = 1 THEN 1 ELSE 0 END), 
 SUM(CASE WHEN MonthCol = 2 THEN 1 ELSE 0 END),
 . . .
 SUM(CASE WHEN MonthCol = 12 THEN 1 ELSE 0 END)
 FROM #TempTable t;

However, here conditional aggregation is enough as there is only 12 months. So, dynamic approach is not needed.

well this is what you can try here:

Declare a table variable with 2 columns

 declare @mytable table(
 VariableName varchar(255),
 VariableValue int
 )

Now Declare 2 more variables, one to set value and second to iterate through loop

 Declare @value int;
 Declare @month int = 1;

Now do the while loop like this:

 while (@month <= 12 ) --since there are only 12 months
 begin
 set @value = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = cast(@month as varchar));
 insert into @mytable values ('Month'+cast (@month as varchar), value );
 set @month = @month +1;
 end

Now you'll have your DataSet in table form which you can access by using the simple query:

VariableName VariableValue 
----------- -------------- 
 Month1 23
 Month2 19
 Month3 66
 Month4 12
 Month5 05
 Month6 04
 Month7 63 
 Month8 76 
 Month9 21 
 Month10 23 
 Month11 63 
 Month12 12 



Select * from @mytable where VariableName = 'Month1'