mysql - 如何在MYSQL tempTable结果中将行显示为列

Question

Net，MYSQL Web 应用程序。我们已经创建了将返回表的存储过程，但现在我们想要更改该过程的结构以用于显示目的。那么如何使Mysql存储过程的TempTable结果为以下格式呢？行转换为列。

Phoenix 解决方案输出:

Answer 1

尝试

SELECT 
       SUM(CASE WHEN Agedreceivable='Current' THEN AgedreceivableAmt END) Current,
       SUM(CASE WHEN Agedreceivable='1-30' THEN AgedreceivableAmt END) '1-30',
       SUM(CASE WHEN Agedreceivable='31-60' THEN AgedreceivableAmt END) '31-60',
       SUM(CASE WHEN Agedreceivable='61-90' THEN AgedreceivableAmt END) '61-90',
       SUM(CASE WHEN Agedreceivable='>90' THEN AgedreceivableAmt END) '>90'
  FROM
(
  SELECT @n := @n + 1 rownum, agedreceivable, agedreceivableamt
    FROM temp, (SELECT @n := 0) z
) y
 GROUP BY FLOOR((rownum - 1) / 5)

输出:

| CURRENT | 1-30 | 31-60 | 61-90 | >90 |
----------------------------------------
|       0 |    0 |     0 |     0 |   0 |
|   12993 |    0 |     0 |     0 |   0 |

这里是SQLFiddle 演示