Transposing rows into columns (MySQL)

Question

So, lets say I have a table called "imports" that looks like this:

| id | importer_id | total_m | total_f | |====|=============|=========|=========| | 1 | 1 | 100 | 200 | | 1 | 1 | 0 | 200 |

And I need the query to return it pivoted or transposed (rows to columns) in this way:

| total_m | sum(total_m) | | total_f | sum(total_f) |

I can't think on a way to do this without using another table (maybe a temporary table?) and using unions, but there should be a better way to this anyway (maybe with CASE or IF?).

Thanks in advance.

Алексей Мокрев · Accepted Answer · 2017-11-29 05:07:44Z

3

select 'total_m', sum(total_m) from imports union select 'total_f', sum(total_f) from imports

http://sqlfiddle.com/#!9/fc1c0/2/0

answered Nov 29, 2017 at 5:07

Алексей Мокрев

1927 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

demogar Over a year ago

Way that easy. Thanks a lot, I was stumbling with this late at night.

Paul Maxwell · Accepted Answer · 2017-11-29 05:18:09Z

You can "unpivot" by first expanding the number of rows, which is done below by cross joining a 2 row subquery. Then on each of those rows use relevant case expression conditions to align the former columns to the new rows ("conditional aggregates").

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE imports (`id` int, `importer_id` int, `total_m` int, `total_f` int) ; INSERT INTO imports (`id`, `importer_id`, `total_m`, `total_f`) VALUES (1, 1, 100, 200), (1, 1, 0, 200) ;

Query 1:

select * from ( select i.importer_id , concat('total_',cj.unpiv) total_type , sum(case when cj.unpiv = 'm' then total_m when cj.unpiv = 'f' then total_f else 0 end) as total from imports i cross join (select 'm' as unpiv union all select 'f') cj group by i.importer_id , cj.unpiv ) d

Results:

| importer_id | total_type | total | |-------------|------------|-------| | 1 | total_f | 400 | | 1 | total_m | 100 |

Collectives™ on Stack Overflow

Transposing rows into columns (MySQL)

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related