CODEWITHSUNDEEP
mysqlsqldatabasepivotspreadsheet
JMB1656
JMB1656

Reputation: 1

MYSQL - Pivoting Data

The data from my application currently comes in as a series of columns, but I would really like there to only be two columns, RESULTS and TASK. See the attached "Spreadsheet" screenshot for how my current data comes in and the "Database" Screenshot for what I am trying to achieve.

I am currently able to do this with about 100 UNION ALL statements, but my queries become DREADFULLY slow. Is there a better way to achieve this without so many UNION ALLs?

Spreadsheet Database:

Database

Thanks!

Upvotes: 0

Views: 64

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

UNION ALL is a fine approach, but it does require scanning the table once for each subquery.

One alternative uses a cross join. This requires more coding, but it might speed things up:

select tt.task,
       (case when tt.task =  'Use Proper PPE' then use_proper_ppe
             when tt.task = 'Damage Prevention' then damage_prevention
             . . .
        end) as result
from t cross join
     (select 'Use Proper PPE' as task union all
      select 'Damage Prevention' union all
      . . .
     ) tt;

Upvotes: 1

Related Questions