Pivot a table in MySQL and add column headings

Question

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

iamge2

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

Can you share the query which produced the result set with only the two columns `Month` and `Outcomes` ? — Tim Biegeleisen, Aug 10 '16 at 08:33
Hi Tim the second set of results is my idea on what the finished product should look like to hopefully be better understood by SSRS when producing a graph. My current coding results in the first table which i am hoping to pivot and add column headings to produce the second — Mac, Aug 10 '16 at 08:43

Ultimater · Accepted Answer · 2016-08-10T09:11:31.367

0

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;

edited Aug 10 '16 at 09:11

answered Aug 10 '16 at 08:58

Ultimater

4,647
2
29
43

Thanks Ultimater, the numbers will change month by month though. Would I replace say 1040 with 'SumOfCurrentMonth' then in the 'INTO' section put it as @SumOfCurrentMonth? – Mac Aug 10 '16 at 09:15
The hard-coded numbers is a proof of concept. The last snippet I posted would be how to modify your query. You'd just replace the `...` with the rest of your query. Meaning copy-paste from `(SELECT OutcomeTimes.organisation_name` and end with `IfNull(outcome,"No Outcome")) as OutcomeTimes2`. Just make sure to add the semicolon afterwards. – Ultimater Aug 10 '16 at 09:22
also could you give me an idea of how I would incorporate this into my code? would this sit above my current select query? – Mac Aug 10 '16 at 09:24
Sorry I am being dense I know but i cannot see within your snippet the '...' section. would this be after the final 'AS Outcomes'? – Mac Aug 10 '16 at 09:28
Step 1: visit http://pastie.org/pastes/10933950/text Step 2: copy-paste the query there and use it instead of your current query. It replaces it completely. Step 3: run it – Ultimater Aug 10 '16 at 09:30
Thank you, that seems to of worked almost perfectly i am getting some decimalisation though. SumOfCurrentMonth 1121.000000000000000000000000000000 SumOfCurrentLess1 3279.000000000000000000000000000000 SumOfCurrentLess2 3582.000000000000000000000000000000 SumOfCurrentLess3 3200.000000000000000000000000000000 SumOfCurrentLess4 2938.000000000000000000000000000000 SumOfCurrentLess5 2922.000000000000000000000000000000 SumOfCurrentLess6 3479.000000000000000000000000000000 is this normal? – Mac Aug 10 '16 at 09:41
That shouldn't be an issue with the query. Try running it directly in phpMyAdmin and there's no such decimal. I'm guessing it's line graph settings. – Ultimater Aug 10 '16 at 09:49
Thanks Ultimater you've been great, I'm sure I'll figure it out, thanks for all your help and I've marked this as answered – Mac Aug 10 '16 at 10:03

Pivot a table in MySQL and add column headings

1 Answers1