Table 1:
item Purchase price
------- --------------
item1 10$
item2 20$
item3 30$
Table 2:
item Sale Price
------- --------------
item1 10$
item2 20$
item3 30$
I want the following result:
row name item1 item2 item3 total
-------------- ------ ------ ------ ------
Purchase price 10$ 20$ 30$ 60$
Sale price 15$ 25$ 35$ 75$
Profit 5$ 5$ 5$ 15$
Thanks
You need a third table to aggregate purchase and sale prices for each item. Then, you compute profits and totals and rotate the rows so that each item becomes a column.
Check the script bellow (comments included) but remember that for large tables it might take a while to get the results.
--setup test variables
declare @table1 table(item varchar(50), purchase int)
declare @table2 table(item varchar(50), sale int)
insert into @table1 values ('item1', 10)
insert into @table1 values ('item2', 20)
insert into @table1 values ('item3', 30)
insert into @table2 values ('item1', 15)
insert into @table2 values ('item2', 25)
insert into @table2 values ('item3', 35)
--create a temporary table to hold each item's purchase price, sale price and profit
create table #sumTable (row varchar(50), item varchar(50), price int)
--insert purchase prices from table1
insert into #sumTable
select 'purchase', item, purchase from @table1
--insert sale prices from table2
insert into #sumTable
select 'sale', item, sale from @table2
--calculate profits for each item
insert into #sumTable
select 'profit', t1.item, sale - purchase
from @table1 t1
inner join @table2 t2 on t1.item = t2.item
--calculate totals
insert into #sumTable
select row, 'total', sum(price)
from #sumTable
group by row, item
--check what we have so far (uncomment if needed)
--select * from #sumTable
--roate the rows by row type, so that each item's name becomes a row
declare @sql nvarchar(max),
@cols nvarchar(max)
select @cols = STUFF(
(select ',
SUM(CASE WHEN [item] = ''' + [item] + ''' THEN [price] ELSE NULL END) AS [' + [item] + ']'
from #sumTable
group by [item]
order by [item]
for xml path(''),type).value('.','varchar(max)'), 1, 2, '')
set @sql = 'SELECT [row], ' + @cols + ' FROM #sumTable GROUP BY [row]'
execute(@sql)
--drop the temporary table
drop table #sumTable
The result looks like this
row item1 item2 item3 total
----------- ----------- ----------- ----------- -------
profit 5 5 5 15
purchase 10 20 30 60
sale 15 25 35 75