Faced the problem of combining a one-to-many view table with a table of values. I do not know how to formulate a question correctly, therefore I will give an example of a table. There is a one-to-many table view.

| n | v | --------- | 1 | x | | 1 | y | | 1 | z | | 2 | x | | 2 | z | | 3 | y |

And just a table of unique values ​​of the form.

 | t | ----- | x | | y | | z |

And at the output I want to get a table of the form

 | n | v | t | ------------- | 1 | x | x | | 1 | y | y | | 1 | z | z | | 2 | x | x | | 2 | - | y | | 2 | z | z | | 3 | - | x | | 3 | y | y | | 3 | - | z |

Where dashes are NULL. Roughly speaking, I need to combine two tables with each other in such a way that the merging takes place for each unique number n separately. I would not like to use a loop for each unique n and join separately, since the data volume is large (and in the future it will be many times more), and the cycles in MSSQL work too slowly. I also tried to solve the problem with recursive CTE, but I had an inadequate table. Perhaps someone faced with such a task and / or knows some concise solution. Sorry for the crooked question and thanks!

    1 answer 1

     select distinct t1.n, tt1.v, t2.t from t1 left join t2 on 1 = 1 left join t1 as tt1 on t2.t=tt1.v and t1.n=tt1.n order by t1.n -- если нужна сортировка