Transpose dynamically using MySQL queries

Question

I’m having some trouble while transposing some table rows into columns based on joins using MySQL. I know it’s a database issue, but maybe someone could give me an alternative solution.

Given the following two tables:

INVOICES

COLUMNS

I need to output this table:

Basically, on the first table I save the information of different invoices and the ID of the column where the Total of each invoice should be shown. On the second table I’m saving the different columns I have. The query should be dynamic, so if I create new columns on the second table I wouldn’t have to update my query.

Please see [Why should I provide an MCRE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Mar 03 '21 at 16:20
This is PIVOT, not transpose. But your case is simple - use 3 separate expressions with according CASE. — Akina, Mar 03 '21 at 16:24

score 0 · Accepted Answer · answered Mar 03 '21 at 16:26

0

SELECT id_invoice,
       CASE WHEN description = 'A' THEN total ELSE '' END a,
       CASE WHEN description = 'B' THEN total ELSE '' END b,
       CASE WHEN description = 'C' THEN total ELSE '' END c
FROM invoices
JOIN columns USING (id_column)

answered Mar 03 '21 at 16:26

Akina

39,301
5
14
25

Thanks. It worked. But it is not a dynamic query. If I create more rows inside the COLUMNS table, The won't appear. – Carlos Salvucci Mar 04 '21 at 01:08
@CarlosSalvucci Use, for example, https://stackoverflow.com/a/66136355/10138734 – Akina Mar 04 '21 at 04:46

Transpose dynamically using MySQL queries

1 Answers1