Mysql 行转列

* from t_zg_asset_statistic_info a where a.BATCH_NO=‘ss20170123181418‘ ;

这是原始数据：

这是行转列后的样子：

select
 a.DEBT_TYPE, 
sum(case  WHEN DEBT_TYPE =‘0‘  THEN AMOUNT END) ‘初债‘,
sum(case  WHEN DEBT_TYPE =‘1‘  THEN AMOUNT END) ‘二级债‘ 
,
 CONVERT(group_concat(DEBT_TYPE,‘总量:‘, AMOUNT) USING utf8) as ‘拼接合计‘,
sum(AMOUNT) as ‘合计‘
  from  t_zg_asset_statistic_info a 

where a.BATCH_NO=‘ss20170123181418‘
GROUP BY  a.DEBT_TYPE
ORDER BY a.DEBT_TYPE
;

后记：mysql  BLOB字段转String的方法

CONVERT(group_concat(DEBT_TYPE,‘总量:‘, AMOUNT) USING utf8)

 

Mysql 行转列

标签：.bat   lob   mysql   pre   blob   .com   using   convert   concat