I have two tables in SQL Server, Portfolio and Master. The portfolio holds inventory, while the master table defines the room types and number of bedrooms.

select
    PropertyNumber,
    Unit,
    Rent,
    Code
from Portfolio

The above query returns the following records:

While the below query returns the following:

select
    Property,
    Unit,
    Duplex
from [Master]

I'm trying to split rows based on the Duplex column in my Master table. For example, in the initial output, I'd like to split that first record into two rows based on the 2BD data (and also divide the Rent column by that number). In other words, the final result would look like this:

CodePudding user response：

I won't write your query for you, but I'll tell you how to do it, if I understand the question.

IIUC you want to produce N rows for master based on duplex.

To produce N rows, create a little table, perhaps like this:

select distinct duplex as n into #N 

then

select m.* from master as m join #N
on duplex <= n

The rest is easy: extend the join to portfolio, and divide rent by duplex.