Mac Mac - 3 months ago 7
MySQL Question

Pivot a table in MySQL and add column headings

I have a very small set of results generated in MySQL. These results need to be displayed on an SSRS report line graph, but in their current format this seems to be impossible.

This is the current result set

and this is how I think it may work

I was thinking that by pivoting the table and adding column headers SSRS may be able to understand what I am trying to achieve and display the data correctly.

I have looked on here and elsewhere, but all the work arounds seem to be quite complex. Is there a way of simply pivoting the data and adding column headers, or would I be better off re-working the MySQL to generate the data differently?

This is the code I currently have, I apologise in advance but I am new to this

Select SUM(OutcomeTimes2.CurrentMonth) as SumOfCurrentMonth, SUM(OutcomeTimes2.CurrentLess1) as SumOfCurrentLess1, SUM(OutcomeTimes2.CurrentLess2) as SumOfCurrentLess2, SUM(OutcomeTimes2.CurrentLess3) as SumOfCurrentLess3, SUM(OutcomeTimes2.CurrentLess4) as SumOfCurrentLess4, SUM(OutcomeTimes2.CurrentLess5) as SumOfCurrentLess5, SUM(OutcomeTimes2.CurrentLess6) as SumOfCurrentLess6
from (SELECT OutcomeTimes.organisation_name, OutcomeTimes.organisation_id, OutcomeTimes.name, OutcomeTimes.order_no, ifnull(OutcomeTimes.budgetcode,"No Budget Code")as budgetcode, ifnull(OutcomeTimes.budgetname,"No Budget Name")as budgetname, Sum(OutcomeTimes.Budget_Duration) AS SumOfBudget_Duration, Sum(OutcomeTimes.Actual_Duration) AS SumOfActual_Duration, OutcomeTimes.the_date, Ifnull(outcome,"No Outcome") AS Outcome_rec
, if(date_format(date(OutcomeTimes.the_date), '%m %Y')=date_format(date_sub(now(), interval 0 MONTH), '%m %Y'),1,0) as CurrentMonth
, if(date_format(date(OutcomeTimes.the_date), '%m %Y')=date_format(date_sub(now(), interval 1 MONTH), '%m %Y'),1,0) as CurrentLess1, if(date_format(date(OutcomeTimes.the_date), '%m %Y')=date_format(date_sub(now(), interval 2 MONTH), '%m %Y'),1,0) as CurrentLess2, if(date_format(date(OutcomeTimes.the_date), '%m %Y')=date_format(date_sub(now(), interval 3 MONTH), '%m %Y'),1,0) as CurrentLess3
, if(date_format(date(OutcomeTimes.the_date), '%m %Y')=date_format(date_sub(now(), interval 4 MONTH), '%m %Y'),1,0) as CurrentLess4, if(date_format(date(OutcomeTimes.the_date), '%m %Y')=date_format(date_sub(now(), interval 5 MONTH), '%m %Y'),1,0) as CurrentLess5, if(date_format(date(OutcomeTimes.the_date), '%m %Y')=date_format(date_sub(now(), interval 6 MONTH), '%m %Y'),1,0) as CurrentLess6
FROM (SELECT qry_bookings.organisation_id, qry_bookings.organisation_name, qry_bookings.order_no, qry_bookings.the_date, qry_bookings.start_Time, qry_bookings.end_Time, TIMESTAMPDIFF(MINUTE,start_Time,end_Time) AS Budget_Duration, if(qry_bookings.name ="","No Name",qry_bookings.name) as name , qry_bookings.actual_start_Time, qry_bookings.actual_end_Time, TIMESTAMPDIFF(MINUTE,actual_start_Time,qry_bookings.actual_end_Time) AS Actual_Duration, qry_bookings.budgetcode, qry_bookings.budgetname, qry_bookings.outcome, schedule_overview.outcome_code_desc
FROM qry_bookings INNER JOIN schedule_overview ON qry_bookings.schedule_id = schedule_overview.schedule_id
WHERE ((qry_bookings.business_unit_id="2") AND (qry_bookings.deleted_from_schedule=0) and (qry_bookings.the_date Between CAST(DATE_FORMAT(date_sub(now(), interval 6 MONTH) ,'%Y-%m-01') as DATE) And Now())) and (((qry_bookings.organisation_id)="797007013984") OR
(((qry_bookings.organisation_id)="363079430613984")) OR
(((qry_bookings.organisation_id)="137952779314169")) OR
(((qry_bookings.organisation_id)="996006860914169")) OR
(((qry_bookings.organisation_id)="289833198813984")) OR
(((qry_bookings.organisation_id)="581692616814417")) OR
(((qry_bookings.organisation_id)="70247802713984")) OR
(((qry_bookings.organisation_id)="917771077113984")) OR
(((qry_bookings.organisation_id)="317283772114056")) OR
(((qry_bookings.organisation_id)="592108421914555")) OR
(((qry_bookings.organisation_id)="177551075713984")) OR
(((qry_bookings.organisation_id)="28576585213984")) OR
(((qry_bookings.organisation_id)="180051500814593")) OR
(((qry_bookings.organisation_id)="472612326714612")) OR
(((qry_bookings.organisation_id)="865056550613984")) OR
(((qry_bookings.organisation_id)="50126601513984")) OR
(((qry_bookings.organisation_id)="124179841214194")) OR
(((qry_bookings.organisation_id)="407940379014254")) OR
(((qry_bookings.organisation_id)="409966399013984")) OR
(((qry_bookings.organisation_id)="747474374413984")) OR
(((qry_bookings.organisation_id)="788147281813987")))
ORDER BY qry_bookings.organisation_name, qry_bookings.name) AS OutcomeTimes
GROUP BY OutcomeTimes.organisation_name, OutcomeTimes.name, OutcomeTimes.order_no, OutcomeTimes.budgetcode, OutcomeTimes.budgetname, OutcomeTimes.the_date, IfNull(outcome,"No Outcome")) as OutcomeTimes2


Thank you in advance, you guys are brilliant and have always been of great help

Answer

Considering the complexity of your SELECT query, for simplicity's sake, let's say your query was:

SELECT 1040 AS 'a', 3279 AS 'b', 3582 AS 'c';

An easy solution would be to use SELECT INTO to store the columns as variables then SELECT the variables as you please afterwards with another query:

SELECT 1040 AS 'a', 3279 AS 'b', 3582 AS 'c' INTO @a, @b, @c;
SELECT 'SumOfCurrentMonth' AS 'Month', @a AS Outcomes UNION ALL
SELECT 'SumOfCurrentLess1' AS 'Month', @b AS Outcomes UNION ALL
SELECT 'SumOfCurrentLess2' AS 'Month', @c AS Outcomes

In other words:

SELECT SUM(OutcomeTimes2.CurrentMonth) as SumOfCurrentMonth,
       SUM(OutcomeTimes2.CurrentLess1) as SumOfCurrentLess1,
       SUM(OutcomeTimes2.CurrentLess2) as SumOfCurrentLess2,
       SUM(OutcomeTimes2.CurrentLess3) as SumOfCurrentLess3,
       SUM(OutcomeTimes2.CurrentLess4) as SumOfCurrentLess4,
       SUM(OutcomeTimes2.CurrentLess5) as SumOfCurrentLess5,
       SUM(OutcomeTimes2.CurrentLess6) as SumOfCurrentLess6
       INTO @a,@b,@c,@d,@e,@f,@g
FROM ... ;
SELECT 'SumOfCurrentMonth' AS 'Month', @a AS Outcomes UNION ALL
SELECT 'SumOfCurrentLess1' AS 'Month', @b AS Outcomes UNION ALL
SELECT 'SumOfCurrentLess2' AS 'Month', @c AS Outcomes UNION ALL
SELECT 'SumOfCurrentLess3' AS 'Month', @d AS Outcomes UNION ALL
SELECT 'SumOfCurrentLess4' AS 'Month', @e AS Outcomes UNION ALL
SELECT 'SumOfCurrentLess5' AS 'Month', @f AS Outcomes UNION ALL
SELECT 'SumOfCurrentLess6' AS 'Month', @g AS Outcomes;
Comments