Storing results in tables with dynamic names

Question

Storing results in tables with dynamic names

Petterco

SSC Veteran

Points: 268
More actions
January 21, 2020 at 12:31 pm

#3716992
Hello,
I have this loop which creates a bunch of matrices based on the month_nbr (in this case 1-36).
What i want to do is to store the 36 results from this loop into separate tables with the names
Matrix_1, matrix_2 and so on. I need these tables stored as i will multiply them using matrix multiplication later on.
This is the basic code which outputs the 36 results (which i struggle to store)
declare @P as int = 0
WHILE @p <= 36
begin
set @p=@p+1
select x_group, [1], [2], [3], [4], [5],[6], [7] from #predicted_transition_matrix where month_nbr=@p
end
How hard can it be.. spent 3 hours googling and trying stuff out. but im still going nowhere.
Any suggestions ?
- This topic was modified 4 years, 10 months ago by Petterco.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 1

im not 100% sure what you exactly want however, i would recommend you reading Jeff Moden article on tally tables and remove your loop.

https://www.sqlservercentral.com/articles/the-numbers-or-tally-table-what-it-is-and-how-it-replaces-a-loop-1

***The first step is always the hardest *******

Neil Burton SSC-Insane Points: 22999 More actions · Answer 2

If you're after 36 individual matrices a loop may indeed be the way to go. However, you can't just create loop to execute a SELECT statement with 36 different values, you'll need something like this.

DECLARE
@SQLNVARCHAR(300)
,@PTINYINT =1;

WHILE @P <= 36
BEGIN 


SET @SQL = N'x_group, [1], [2], [3], [4], [5],[6], [7] from #predicted_transition_matrix where month_nbr=@p' -- Create a new query for each value of @p

EXEC sp_executesql @SQL,N'@p TINYINT', @P  -- Actually executes the SQL created above
SET @P +=1
END

In most cases, a loop isn't needed but for multiple executions of something, it can do the trick.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

Can't you multiply two matrices together from the same table?

I don't see why you need two different tables?

Petterco SSC Veteran Points: 268 More actions · Answer 4

Thanks Neil. Will be helpful further on. Im new working in SSMS coming from a SAS / python world.

Yeah, as you mention Jonathan, I could have multiplied from same table. Thats what i went on to do, but stupid as I am I then realised that I actually need to use the product from the first multiplication to move forward.

I would use the inital result to calculate the next one. So now I'm trying to figure out how to create something to do that instead..

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 5

Petterco wrote:

Yeah, as you mention Jonathan, I could have multiplied from same table. Thats what i went on to do, but stupid as I am I then realised that I actually need to use the product from the first multiplication to move forward.
I would use the inital result to calculate the next one. So now I'm trying to figure out how to create something to do that instead..

Can't you insert the next result onto the same table, then query it from that table?

Mike01 SSChampion Points: 11395 More actions · Answer 6

Can you create 1 table with a BatchKey or some identifier to identify which records were loaded with which path. Instead of Matrix_1 and Matrix_2 tables, have a MatrixID of 1 or 2

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Steve Collins SSCrazy Points: 2157 More actions · Answer 7

Maybe successively cross apply with partial joins? Something like:

with
matrix_1(month_nbr, x_group, [1], [2], [3]) as (
    select 1, 'grp a', 1, 1, 1
    union all
    select 1, 'grp a', 1, 1, 1
    union all
    select 1, 'grp b', 1, 1, 1
    union all
    select 1, 'grp b', 1, 1, 1
    union all
    select 2, 'grp a', 1, 1, 1),
matrix_2(x_group, const) as (
    select 'grp a', 12.2
    union all
    select 'grp b', 10.4),
matrix_3(month_nbr, const) as (
    select 1, 2.3
    union
    select 2, 1.7)
select
  m1.*, m2.*, m3.*
from
  matrix_1 m1
 cross apply 
  (select 
     m1.[1]*m2.const, m1.[2]*m2.const, m1.[3]*m2.const
   from
     matrix_2 m2
   where
     m1.x_group=m2.x_group) m2([1], [2], [3]) 
 cross apply 
  (select 
     m2.[1]*m3.const, m2.[2]*m3.const, m2.[3]*m3.const
   from
     matrix_3 m3
   where
     m1.month_nbr=m3.month_nbr) m3([1], [2], [3]);

Also, maybe if you unpivot the #predicted_transition_matrix table so that the columns labelled 1-7 became row values it becomes easier to calculate?

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können