项目中应用的行列转换(存储过程)

将所有的费用列横向显示

 

create proc sp_searchFeeByCommLetterId
as
 declare @sql varchar(8000)
        set @sql = 'SELECT max(Fi.comm_letter_id) as commLetterId'
  select @sql = @sql + ',sum(case fi.name when ''' + name + ''' then amount else 0 end) ' + ltrim(rtrim(name)) + ''
  from (select distinct name from FEE_ITEM) as a
  set @sql = @sql + ' from FEE_ITEM fi,LOG_COMMISSION_LETTERS lcl,FEE_CONFIRM_ITEM fci
   WHERE lcl.COMM_LETTER_ID=fi.COMM_LETTER_ID and fi.fee_c_item_id=fci.id 
   GROUP BY Fi.comm_letter_id'  
  print @sql
  exec(@sql)
GO

 

静态统计费用列

StringBuffer sql=new StringBuffer();
  sql.append("SELECT dc.CUSTOMER_NAME_ALL AS customer,");
  sql.append("sum(CASE fci.NAME WHEN '入库操作费' THEN fci.FEE_AMOUNT ELSE 0 end) wInFee,");
  sql.append("sum(CASE fci.NAME WHEN '出库操作费' THEN fci.FEE_AMOUNT ELSE 0 end) wOutFee,");
  sql.append("sum(CASE fci.NAME WHEN '卸车费' THEN fci.FEE_AMOUNT ELSE 0 END) unloadFee,");
  sql.append("sum(CASE fci.NAME WHEN '装车费' THEN fci.FEE_AMOUNT ELSE 0 END) loadFee,");
  sql.append("sum(CASE fci.NAME WHEN '掏箱费' THEN fci.FEE_AMOUNT ELSE 0 END) unstuffingFee,");
  sql.append("sum(CASE fci.NAME WHEN '装箱费' THEN fci.FEE_AMOUNT ELSE 0 END) stuffingFee,");
  sql.append("sum(CASE fci.NAME WHEN '仓储费' THEN fci.FEE_AMOUNT ELSE 0 end) storageFee,");
  sql.append("sum(CASE fci.NAME WHEN '场地租用费' THEN fci.FEE_AMOUNT ELSE 0 end) rentFee,");
  sql.append("sum(CASE fci.NAME WHEN '加工费' THEN fci.FEE_AMOUNT ELSE 0 END) processingFee,");
  sql.append("sum(CASE fci.NAME WHEN '分拣费' THEN fci.FEE_AMOUNT ELSE 0 END) sortingFee,");
  sql.append("sum(CASE fci.NAME WHEN '包装费' THEN fci.FEE_AMOUNT ELSE 0 END) packingFee,");
  sql.append("sum(CASE fci.NAME WHEN '移库费' THEN fci.FEE_AMOUNT ELSE 0 END) stockTransferFee,");
  sql.append("sum(CASE fci.NAME WHEN '贴标费' THEN fci.FEE_AMOUNT ELSE 0 END) labelingFee,");
  sql.append("sum(CASE fci.NAME WHEN '报关费' THEN fci.FEE_AMOUNT ELSE 0 END) customsClearanceFee,");
  sql.append("sum(CASE fci.NAME WHEN '报关申请单' THEN fci.FEE_AMOUNT ELSE 0 END) billDocumentFee,");
  sql.append("sum(CASE fci.NAME WHEN '企业备案' THEN fci.FEE_AMOUNT ELSE 0 END) companyRFee,");
  sql.append("sum(CASE fci.NAME WHEN '料件备案' THEN fci.FEE_AMOUNT ELSE 0 END) materialRFee,");
  sql.append("sum(CASE fci.NAME WHEN '商检查验费' THEN fci.FEE_AMOUNT ELSE 0 END) inspectFee,");
  sql.append("sum(CASE fci.NAME WHEN '运输费' THEN fci.FEE_AMOUNT ELSE 0 END) transportFee ");
  sql.append("FROM DATA_CUSTOMER dc,FEE_CONFIRM_ITEM fci WHERE dc.CUSTOMER_ID=Fci.CUSTOM_ID ");
  sql.append("GROUP BY dc.CUSTOMER_NAME_ALL");