mysql 两列行转,Mysql MySQL或PHP将行动态转换为两列

But I have to restructure my table now and this is the output

It's pretty much the same with the link above but I to include the AS_amount.anyone can help me?

I want the result below but with AS_month_2016...etc in the right side of each FA_mont2016

This is my code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `display_annualize_table`()

BEGIN

SET group_concat_max_len=10028;

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT CONCAT(

'MAX(IF(month = ''',

month,

''' and year(date) = ',

year(date),

', FS_amount, NULL)) AS `',

CONCAT('FA_',month),

'_',

year(date),

'`')

order by date

) INTO @sql

FROM tmp_results;

if coalesce(@sql,'') != '' then

set @sql = concat(', ', @sql);

end if;

SET @sql = CONCAT(

'SELECT r.account as Account,

r.region as Region ',

coalesce(@sql,''),

'FROM tmp_results r

LEFT JOIN accounts AS a

on r.account_id = a.id

GROUP BY r.account, r.region

ORDER By r.account_id');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END

Need your help please. Thank you in advance!

解决方案

It's works the same way as FS_amount, just add the new columns to your code that generates the dynamic columns:

SELECT GROUP_CONCAT(DISTINCT CONCAT(

'MAX(IF(month = ''',

month,

''' and year(date) = ',

year(date),

', FS_amount, NULL)) AS `',

CONCAT('FA_',month),

'_',

year(date),

'`, ',

'MAX(IF(month = ''',

month,

''' and year(date) = ',

year(date),

', AS_amount, NULL)) AS `',

CONCAT('AS_',month),

'_',

year(date),

'`'

)

order by date

) INTO @sql

FROM tmp_results;

You should have a look at the code the statements create (e.g. by temporary adding a select @sql;), although it should be pretty straight forward to add even more columns in case you'll need them.