Separating columns onto rows (pivot table?)

Question

Given a table row such as:

id  |  username  |  att7  |  att28  |  atttotal
1   |   12345    |   77   |    88   |     99

Is there a way in SQL (I am using MySQL at the moment, but can be any database really) to convert that so that the output in my VIEW would be something like:

id  |  username  |  type  |  period      | value
1   |   12345    |   Att  |    7 Days    |  77
2   |   12345    |   Att  |    28 Days   |  88
3   |   12345    |   Att  |    Total     |  99

I am reading about PIVOT tables at the moment, as I feel like that might be what is needed, but so far am not able to work out how I'd actually go about this..

Cheers.

possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — ninesided, Mar 20 '14 at 15:25
yes, all the examples I could find were in the opposite direction, so I couldn't work out how to do it this way. — CMR, Mar 20 '14 at 15:29

score 1 · Answer 1 · answered Mar 20 '14 at 15:25

This is an unpivot. The naive way to do it is with union all:

create view v as
    select id, username, 'Att' as type, '7 days' as period, att7 from table union all
    select id, username, 'Att' as type, '28 days' as period, att28 from table union all
    select id, username, 'Att' as type, 'total' as period, total from table;

There are more efficient ways to write such a query, if your table is large.

Separating columns onto rows (pivot table?)

1 Answers1