CODEWITHSUNDEEP
mysqlsql
CMR
CMR

Reputation: 1416

Separating columns onto rows (pivot table?)

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.

Upvotes: 0

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

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.

Upvotes: 1

Related Questions