[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1559
  • Last Modified:

MYSQL / PHP Transpose Rows to Columns

I have included a sample database for this query with full create and insert commands. 2 tables, 2 rows on one table, 8 on another.

One table is periods, the other is period data, Using the following (kindly supplied on this site) MYSQL command I can successfully draw the latest 2 periods of data. 4 weeks in each. These are displayed as rows of data.. HOWEVER I want to generate these rows (using php or mysql.. or whatever it takes) so that the php page displays the data vertically to fit a table such as demonstrated in the image included.

The MYSQL statement I use to pull of the 2 period data is

SELECT *
FROM mfugi_kpi_perioddata period1
WHERE period1.periodnumber = ( SELECT MAX(id) FROM mfugi_kpi_period )
OR period1.periodnumber = ( SELECT MAX(id) FROM mfugi_kpi_period WHERE id < ( SELECT MAX(id) FROM mfugi_kpi_period ) )
ORDER BY period1.periodnumber DESC, weeknumber ASC

Open in new window


All advise / code appreciated.
The MYSQL is in a JOOMLA MODEL, and the display will be in a VIEW

 end result required sampledatabase.sql
0
McDeathUK
Asked:
McDeathUK
  • 2
2 Solutions
 
Ray PaseurCommented:
Could you please replace SELECT * with a SELECT query that names the actual columns you intend to use?  And please post the CREATE TABLE statements, thanks.
0
 
johanntagleCommented:
I'm guessing kpi01target, etc are the ones that will define the rows.  Guess for each one, there will be one SQL:

To make it cleaner, I suggest you do it in steps:

Get Period 2:
SELECT MAX(id) FROM mfugi_kpi_period; -- store this in a PHP variable

Get Period 1:
SELECT MAX(id) FROM mfugi_kpi_period WHERE id < $period2;  -- store this in a PHP variable

For each KPI, execute:
SELECT if(period_number=$period1 and weeknumber=1, kpi01value,0) as p1w1,
           if(period_number=$period1 and weeknumber=2, kpi01value,0) as p1w2,
           if(period_number=$period1 and weeknumber=3, kpi01value,0) as p1w3,
           if(period_number=$period1 and weeknumber=4, kpi01value,0) as p1w4,
           if(period_number=$period2 and weeknumber=1, kpi01value,0) as p1w1,
           if(period_number=$period2 and weeknumber=2, kpi01value,0) as p1w2,
           if(period_number=$period2 and weeknumber=3, kpi01value,0) as p1w3,
           if(period_number=$period2 and weeknumber=4, kpi01value,0) as p1w4
FROM mfugi_kpi_perioddata period1
WHERE period1.periodnumber = $period1
OR period1.periodnumber = $period2;

Repeat the above for each KPI, replacing kpi01value with appropriate column.

I put the period values to variables so you won't have to do multiple subselects.  

It would have been easier if instead of one column for each KPI, you had kpi_number and kpi_value.  This way you can do it in one statement instead of one call for each KPI:

SELECT t.kpi_number, t.p1w1, t.p1w2, t.p1w3, t.p1w4, t.p2w1, t.p2w2, t.p2w3, t.p2w4 FROM
(SELECT kpi_number, if(period_number=$period1 and weeknumber=1, kpi_value,0) as p1w1,
           if(period_number=$period1 and weeknumber=2, kpi_value,0) as p1w2,
           if(period_number=$period1 and weeknumber=3, kpi_value,0) as p1w3,
           if(period_number=$period1 and weeknumber=4, kpi_value,0) as p1w4,
           if(period_number=$period2 and weeknumber=1, kpi_value,0) as p1w1,
           if(period_number=$period2 and weeknumber=2, kpi_value,0) as p1w2,
           if(period_number=$period2 and weeknumber=3, kpi_value,0) as p1w3,
           if(period_number=$period2 and weeknumber=4, kpi_value,0) as p1w4
FROM mfugi_kpi_perioddata period1
WHERE period1.periodnumber = $period1
OR period1.periodnumber = $period2
) t
group by t.kpi_number order by t.kpi_number;
0
 
johanntagleCommented:
Sorry, some errors:  The first SQL should be:

SELECT sum(t.p1w1), sum(t.p1w2), sum(t.p1w3), sum(t.p1w4), sum(t.p2w1), sum(t.p2w2), sum(t.p2w3), sum(t.p2w4) FROM
(SELECT if(period_number=$period1 and weeknumber=1, kpi01value,0) as p1w1,
           if(period_number=$period1 and weeknumber=2, kpi01value,0) as p1w2,
           if(period_number=$period1 and weeknumber=3, kpi01value,0) as p1w3,
           if(period_number=$period1 and weeknumber=4, kpi01value,0) as p1w4,
           if(period_number=$period2 and weeknumber=1, kpi01value,0) as p1w1,
           if(period_number=$period2 and weeknumber=2, kpi01value,0) as p1w2,
           if(period_number=$period2 and weeknumber=3, kpi01value,0) as p1w3,
           if(period_number=$period2 and weeknumber=4, kpi01value,0) as p1w4
FROM mfugi_kpi_perioddata period1
WHERE period1.periodnumber = $period1
OR period1.periodnumber = $period2) t;

Open in new window


while the alternative I'm proposing should be:

SELECT t.kpi_number, sum(t.p1w1), sum(t.p1w2), sum(t.p1w3), sum(t.p1w4), sum(t.p2w1), sum(t.p2w2), sum(t.p2w3), sum(t.p2w4) FROM
(SELECT kpi_number, if(period_number=$period1 and weeknumber=1, kpi_value,0) as p1w1,
           if(period_number=$period1 and weeknumber=2, kpi_value,0) as p1w2,
           if(period_number=$period1 and weeknumber=3, kpi_value,0) as p1w3,
           if(period_number=$period1 and weeknumber=4, kpi_value,0) as p1w4,
           if(period_number=$period2 and weeknumber=1, kpi_value,0) as p1w1,
           if(period_number=$period2 and weeknumber=2, kpi_value,0) as p1w2,
           if(period_number=$period2 and weeknumber=3, kpi_value,0) as p1w3,
           if(period_number=$period2 and weeknumber=4, kpi_value,0) as p1w4
FROM mfugi_kpi_perioddata period1
WHERE period1.periodnumber = $period1
OR period1.periodnumber = $period2
) t
group by t.kpi_number order by t.kpi_number;

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
It looks like you got the answer above, but also that you can save the derived table and just do conditional aggregates, i.e., SUM(IF(...)).
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_3527-A-SQL-Tidbit-Conditional-Aggregates.html
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now