CODEWITHSUNDEEP
mysqlsqldatabase
demogar
demogar

Reputation: 711

Transposing rows into columns (MySQL)

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.

Upvotes: 0

Views: 54

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35583

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 |

Upvotes: 0

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

Reputation: 192

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

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

Upvotes: 3

Related Questions