Storing results in tables with dynamic names

  • Petterco

    SSC Veteran

    Points: 268

    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 8 months ago by  Petterco.
  • SGT_squeequal

    SSCertifiable

    Points: 7163

    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: 22249

    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

  • Jonathan AC Roberts

    SSCoach

    Points: 17334

    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

    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: 17334

    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: 11284

    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

    Ten Centuries

    Points: 1074

    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