Mysql Row and column conversions

One, the first kind

Original Data Sheet

　　

After conversion

　　

DROP TABLE IF EXISTStempdynamic;CREATE  Temporary TABLETempdynamic (SELECTP.fspaymentname,sr.fspaymentid,sh.fsshiftname,SUM(Sr.fdrecemoney) asAmttotal fromTbsell assINNER JOINTbsellreceive asSr onS.fssellno=Sr.fssellno andS.fsshopguid=Sr.fsshopguidINNER JOINTbpayment asP onSr.fspaymentid=P.fspaymentid andP.fsshopguid=S.fsshopguid andP.fistatus=1INNER JOINTbsellcheck asSc onSc.fssellno=S.fssellno andS.fsshopguid=Sc.fsshopguidINNER JOINTbshift asSh onSc.fsshiftid=Sh.fsshiftid andSh.fistatus=1WHERES.fibillstatus=3 andS.fsshopguid='C5f0d5c9d8b349f493c180bcc22d9ce0' andS.fsselldate='2016-01-15'GROUP  bySr.fspaymentid,sc.fsshiftid);SET @EE="';Set @str_tmp="'; SELECT   @EE:=CONCAT (@EE,'SUM (IF (fsshiftname=\"', Fsshiftname,'\"',', amttotal,0)) as', Fsshiftname,',') asAa into @str_tmp from ( SELECT DISTINCTFsshiftname fromtempdynamic) AOrder  byLength (AA)descLimit1; SET @QQ=CONCAT ('SELECT ifnull (fspaymentname,\'Total\') as account name,',@str_tmp,'sum (amttotal) as totals from Tempdynamic Group by Fspaymentname with ROLLUP'); PREPAREstmt from @QQ; EXECUTEstmt;deallocate Preparestmt

View Code

Ii. the second type of

Original Data Sheet

　　

After conversion

　　

1 DROP TABLE IF EXISTStempdynamic;2 CREATE    TABLETempdynamic (3   SELECTSh.fsshiftname,sh.fsshiftid,SUM(S.fdsaleamt) asSales Amount,SUM(S.fddiscountamt) asDiscount Amount,SUM(S.fdserviceamt) asService charge Amount,SUM(S.fdexpamt) asAmount receivable,SUM(S.fdrealamt) asAmount Paid4   fromTbsell ass5 INNER JOINTbsellcheck asSc onSc.fssellno=S.fssellno andS.fsshopguid=Sc.fsshopguid6 INNER JOINTbshift asSh onSc.fsshiftid=Sh.fsshiftid andSh.fistatus=17 WHERES.fibillstatus=38  andS.fsshopguid='C5f0d5c9d8b349f493c180bcc22d9ce0'9  andS.fsselldate='2016-01-15'Ten GROUP  bySc.fsshiftid); One   A DROP TABLE IF EXISTSTempdynamica; - CREATE  Temporary TABLETempdynamica ( - SELECTA.*  from ( the SELECTFsshiftname,'Sales Amount'    asAccount, Sales Amount asAmttotal fromTempdynamicUNION    All  - SELECTFsshiftname,'Discount Amount'    asAccount, discount Amount asAmttotal fromTempdynamicUNION    All  - SELECTFsshiftname,'Service charge Amount'  asAccount, Service charge amount asAmttotal fromTempdynamicUNION    All - SELECTFsshiftname,'Receivable Amount'    asAccount, receivable Amount asAmttotal fromTempdynamicUNION    All + SELECTFsshiftname,'Amount Paid'    asAccount, Amount paid asAmttotal fromtempdynamic - ORDER  byFsshiftname, subjectsDESC) asa); +  A   at SET @EE="'; - Set @str_tmp="'; - SELECT  @EE:=CONCAT (@EE,'SUM (IF (fsshiftname=\"', Fsshiftname,'\"',', Amttotal, 0)) as', Fsshiftname,',') asAa into @str_tmp -  from ( -  SELECT DISTINCTFsshiftname fromtempdynamic -) AOrder  byLength (AA)descLimit1; in SET @QQ=CONCAT ('SELECT ifnull (account, \'Total\') as account name,',@str_tmp,'sum (amttotal) as totals from Tempdynamica Group by account with ROLLUP'); -  PREPAREstmt from @QQ;  to EXECUTEstmt; + deallocate Preparestmt

View Code

Mysql Row and column conversions