There are two tables Tab1 and Tab2. Table 1 contains a list of numbers, for example:

100 101 102 ... 110

Table 2 contains a list of categories (column 1) and numerical matches (column 2), for example:

 | Кат1 | 3 | | Кат2 | 2 | | Кат3 | 5 | | Кат4 | 1 |

It is necessary to obtain the resulting table Tab3, which will contain a list of numbers from Tab1 and a list of correspondences of categories from Tab2. The index from column 1 indicates how many numbers from Tab1 belong to the specified category. The resulting table should look like this:

 | 100 | Кат1 | | 101 | Кат1 | | 102 | Кат1 | | 103 | Кат2 | | 104 | Кат2 | | 105 | Кат3 | | 106 | Кат3 | | 107 | Кат3 | | 108 | Кат3 | | 109 | Кат3 | | 110 | Кат4 |

Actually, the problem is that I do not understand the principle by which Tab1 and Tab2 can be linked in order to get a result.

    2 answers 2

    To solve the problem, you will need to calculate the ordinal number of each row from the table of Tab1, and also calculate the cumulative result in the second table, which rows should be added.

     with [Таб1] as ( select tab.Id from (values (100), (101), (102), (103), (104), (105), (106), (107), (108), (109), (110)) as tab (Id) ), [Таб2] as ( select tab.Name, tab.Cnt from (values ('Кат1', 3), ('Кат2', 2), ('Кат3', 5), ('Кат4', 1)) as tab (Name, Cnt) ) select tab1.Id, tab2.Name from (select name , sum(Cnt) over (order by name) + 1 - Cnt as Cnt_From , sum(Cnt) over (order by name) as Cnt_To from [Таб2]) as tab2 inner join (select id, row_number() over (order by Id) as rn from [Таб1]) as tab1 on tab1.rn between tab2.Cnt_From and tab2.Cnt_To
    • Thank you. Exactly what is needed. - Tata Skliar

    You can add a field to Tab1 that is the same as the second column in Tab3. And there will be third-party keys from Tab2. So you will connect Tab1 and Tab2

    • It is yes, but now I can only add it manually. For the number of numbers from the example, this is ok, but if the values ​​in Tab 1 are several thousand, it is already more difficult. - Tata Skliar