Right now this is what the query returns.

https://i.stack.imgur.com/XAgoc.png

Its one row with 4 columns.

I need it to return 4 rows with gift amount divided by four as shown below.

https://i.stack.imgur.com/lDSfa.png

There are several thousand rows that each need its own 4 rows.

CodePudding user response：

One idea is to essentially duplicate the row four times, and then divide the gift card amount in each one by four. We can conveniently do this by creating a small table with the number 4 in four rows:

-- ---------
-- TEST DATA
-- ---------
drop table if exists #temp, #numbers

create table #temp (
    Company_Code int, Company_Number int,
    Company_Value int, Gift_Amount int)

create table #numbers (
    [value] decimal(9,3))

insert into #temp 
    values (11520,3520,1150,1250)

insert into #numbers 
    values (4),(4),(4),(4)

-- ---------
-- THE QUERY
-- ---------
select 
    #temp.*, 
    #temp.Gift_Amount / #numbers.[value] as New_Gift_Amount
    from #temp
    cross join #numbers

Result: