ordertable
orderid ordername
1 name1
2 name2
feetype
feeid feetype
1 fee1
2 fee2
accountTable
amountid amount orderid feeid
1 100 1 1
2 101 1 2
3 102 2 1
4 103 2 2
Expect result
ordername fee1name fee1amount fee2name fee2amount
name1 fee1 100 fee2 101
name2 fee1 102 fee2 103
SQL
you want to select the mount of every fee type for each order.
1. so firstly you need change all fee type name in row mode to column mode.
For change, you may need declare varchar to store.
declare @sql varchar(8000)
set @sql = 'select ordername,'
select
'name, ' from feetype
set @sql = left(@sql,len(@sql)-1)
set @sql = @sql + ' from feetype'
print(@sql)
In this sql, you will select every type in table feetype, and change them to column.
2. But you need select the type which was exist in the accountable. so you need add condition.
declare @sql varchar(8000)
set @sql = 'select ordername,'
select @sql = @sql + 'max(case feename when ''' + feename + ''' then feename else '''' end) as ' + feename +'name, '
from
(
select distinct f.feename
from accounttable a
left join feetype f
on a.feeid = f.feeid
)t
set @sql = left(@sql,len(@sql)-1)
set @sql = @sql + ' from feetype'
print(@sql)
3. At last we need select order and amount info, so need modify the select table.
declare @sql varchar(8000)
set @sql = 'select ordername,'
select @sql = @sql + 'max(case feename when ''' + feename + ''' then feename else '''' end) as ' + feename +'name, '
+ 'max(case feename when ''' + feename + ''' then amount else 0 end) as ' + feename + 'amount,'
from
(
select distinct f.feename
from accounttable a
left join feetype f
on a.feeid = f.feeid
)t
set @sql = left(@sql,len(@sql)-1)
set @sql = @sql + ' from (
select a.amount,o.ordername,f.feename from accounttable a
left join ordertable o on o.orderid = a.orderid
left join feetype f on a.feeid = f.feeid
)t group by ordername'
print(@sql)
So now you should get the expect result
@sql = @sql + 'max(case feename when ''' + feename + ''' then feename else '''' end) as ' + feename +