Kevin Rodriguez Kevin Rodriguez - 9 months ago 45
MySQL Question

Crosstab/pivot query with different number of columns depending on a field

I need to pivot my tables with a set of column headers that varies based on one of the fields in the output.

This is what it looks like currently:

id | Name | Test Name | Test Performed | Test Value
1 John HIV EIA Reactive
1 John HIV EIA2 Reactive
1 John HIV WB Positive
1 John HIV Final Kit Positive
2 Mark HIV Rapid Reactive
2 Mark HIV EIA Reactive
2 Mark HIV EIA2 Reactive
2 Mark HIV Final Result Positive
2 Mark Immuno CD4% 12.0%
2 Mark Immuno CD4 Lympo 1299
2 Mark Immuni CD4 Absolute 838
3 Carl SY RPR Reactive
3. Carl SY TPHA Reactive

I want it to be like this, you can see the columns are changing dynamically based on Test Performed:

id | Name | Test Name | EIA | EIA2 | Rapid | WB |Final Kit
1 John HIV Reactive Reactive Positive
2 Mark HIV Reactive Reactive Reactive Reactive

id | Name | Test Name | CD4% | CD4 Lympo | CD4 Absolute
1 Mark Immuno 12.0% 1299 838

id | Name | Test Name | RPR | TPHA
3 Carl SY Reactive Reactive

Answer Source

To make an effectively dynamic-column query like you're looking for requires shenanigans. The way I've done it before† is generally to use one crosstab/pivot query to capture the mapping between column numbers (of which you have up to some suitable maximum N) and final column labels for each entry. The output of this would be something like:

Test Name   |Test Performed |Index
HIV         |EIA            |1
HIV         |EIA2           |2

Then you have another query to map the other way, which comes out like this:

Test Name   |1      |2          |3              |4      |5
HIV         |EIA    |EIA2       |Rapid          |WB     |Final Kit
Immuno      |CD4%   |CD4 Lympo  |CD4 Absolute   |       |
SY          |RPR    |TPHA       |               |       |

Finally, you pivot on Index from the first map joined on Test Name and Test Performed from your initial table to generate the crosstab, and when you display the result, look up the column name/Index in the second map to get Test Performed back.

†Albeit not in either MySQL or C#. Transferrability is fun!

I'll edit later to add sample SQL, once I dig it up and abstract it properly. This should get the general idea across for now, though.