选择每个订单的每种费用的数目，每行纪录为一个订单信息

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 +