Formating data for Funnel Report in MySQL Report

Question

I looking for a way to create a MySQL report for an Funnel visualization.

So my input is a query asking for a project. Like project-id 123456

FROM projects WHERE ID = 123456

Result:

+---------+--------+-----+----------+------+
| Project | Income | Tax | Payments | Rent |
+---------+--------+-----+----------+------+
|  123456 |   2500 |  50 |      250 |  350 |
+---------+--------+-----+----------+------+

For the funnel I need the report like:

+------+----------+-------+
| Step |   Name   | Value |
+------+----------+-------+
|    1 | Income   |  2500 |
|    2 | Tax      |    50 |
|    3 | Payments |   250 |
|    4 | Rent     |   350 |
+------+----------+-------+

Is there a SQL way to put my query content into new columns (step, Name, value).

Thanks

Answer 1

You can use union all to unpivot your table:

select *
fom (
    select 1 step, 'Income' name, Income value from projects where project = 123456
    union all select 2, 'Tax', Tax from projects where project = 123456
    union all select 3, 'Payments', Payments from projects where project = 123456
    union all select 4, 'Rent', Rent from projects where project = 123456
)
order by step