Storing results in tables with dynamic names

  • 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 1 year, 5 months ago by  Petterco.
  • 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 *******

  • 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
    @SQL NVARCHAR(300)
    ,@P TINYINT =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

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

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

  • 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..

     

  • 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?

  • 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/

  • 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

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

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