sql/php - display records horizontally and vertically

Question

So I have this data from basic mySQL select query, that outputs per row.

-Basic mySQL select query

ID---------DATE---------POINT1---POINT2------TOTAL
1----2013-01-03----------10----------16---------26
2----2013-01-03----------11----------22---------33
3----2013-01-03----------15----------7----------22
1----2013-01-04----------20----------4----------24
2----2013-01-04----------8-----------32---------40
3----2013-01-04----------16----------12---------28
1----2013-01-05----------12----------17---------29
2----2013-01-05----------2-----------29---------31
3----2013-01-05----------8-----------10---------18

What I want to do is sort the data by date in columns and by id in rows dynamically, something like per month. here is the desired output,

/----------/2013-01-03/---------/2013-01-04/------/2013-01-05/------/
ID--Point 1-Point2-Total-Point 1-Point2-Total-Point 1-Point2-Total
1----10-------16-----26-----20-------4-----24----12-----17------29
2----11-------22-----33------8------32-----40-----2-----29------31
3----15-------7------22-----16------12-----28-----8-----10------18

then output it to csv or excel. I'm kinda lost on how I can achieve this. If someone can guide me it would be great. thanks

Which RDBMS? The answers vary by system. – Peter Wooster Jan 17 '13 at 01:07 — Peter Wooster, Jan 17 '13 at 01:07

score 0 · Accepted Answer · answered Jan 17 '13 at 01:16

You can unpivot the data in your columns and then perform a pivot to transform all of the data back into columns:

select id,
  sum(case when date='2013-01-03' and col='Point1' then value end) Point1_01032013,
  sum(case when date='2013-01-03' and col='Point2' then value end) Point2_01032013,
  sum(case when date='2013-01-03' and col='Total' then value end) Total_01032013,
  sum(case when date='2013-01-04' and col='Point1' then value end) Point1_01042013,
  sum(case when date='2013-01-04' and col='Point2' then value end) Point2_01042013,
  sum(case when date='2013-01-04' and col='Total' then value end) Total_01042013,
  sum(case when date='2013-01-05' and col='Point1' then value end) Point1_01052013,
  sum(case when date='2013-01-05' and col='Point2' then value end) Point2_01052013,
  sum(case when date='2013-01-05' and col='Total' then value end) Total_01052013
from
(
  select id, date_format(date, '%Y-%m-%d') date, 'Point1' col, Point1 as value
  from yourtable
  union all
  select id, date_format(date, '%Y-%m-%d') date, 'Point2' col, Point2
  from yourtable
  union all
  select id, date_format(date, '%Y-%m-%d') date, 'Total' col, Total
  from yourtable
) src
group by id;

See SQL Fiddle with Demo

The result is:

| ID | POINT1_01032013 | POINT2_01032013 | TOTAL_01032013 | POINT1_01042013 | POINT2_01042013 | TOTAL_01042013 | POINT1_01052013 | POINT2_01052013 | TOTAL_01052013 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  1 |              10 |              16 |             26 |              20 |               4 |             24 |              12 |              17 |             29 |
|  2 |              11 |              22 |             33 |               8 |              32 |             40 |               2 |              29 |             31 |
|  3 |              15 |               7 |             22 |              16 |              12 |             28 |               8 |              10 |             18 |

hi bluefeet, thanks for your reply definitely something to start with. ^_^ — nublet, Jan 17 '13 at 01:28

sql/php - display records horizontally and vertically

1 Answers1