Transpose columns into rows USING MYSQL

Question

i want to convert columns into rows, Is there any way to achieve this. here is my screenshotsenter image description here

my output like this

+----------------------+
| Month       | Data   |
+----------------------+
| data_july   | 130.11 |
| data_august | 257.28 |
+----------------------+

..... n so on.

i am facing a problem to implement this in my query, here is my query:

select
  a.id as milestone_id,
  a.name_of_work,
  case when(a.sch_jul>100) then 100 when (a.sch_jul<0) then 0 else a.sch_jul end as data_july,
  case when(a.sch_aug>100) then 100 when (a.sch_aug<0) then 0 else a.sch_aug end as data_august,
  case when(a.sch_sep>100) then 100 when (a.sch_sep<0) then 0 else a.sch_sep end as data_september,
  case when(a.sch_oct>100) then 100 when (a.sch_oct<0) then 0 else a.sch_oct end as data_october,
  case when(a.sch_nov>100) then 100 when (a.sch_nov<0) then 0 else a.sch_nov end as data_november,
  case when(a.sch_dec>100) then 100 when (a.sch_dec<0) then 0 else a.sch_dec end as data_december,
  case when(a.sch_jan>100) then 100 when (a.sch_jan<0) then 0 else a.sch_jan end as data_january,
  case when(a.sch_feb>100) then 100 when (a.sch_feb<0) then 0 else a.sch_feb end as data_february,
  case when(a.sch_mar>100) then 100 when (a.sch_mar<0) then 0 else a.sch_mar end as data_march,
  case when(a.sch_apr>100) then 100 when (a.sch_apr<0) then 0 else a.sch_apr end as data_april
from
(
  SELECT
    distinct w.id,
    w.name_of_work,
    s.milestone_id,
    round(((DATEDIFF(date_format('2017-07-30', '%Y-%m-%d'), s.start_date) / DATEDIFF(s.end_date, s.start_date)) * 100),2) as sch_jul,
    round(((DATEDIFF(date_format('2017-08-30', '%Y-%m-%d'), s.start_date) / DATEDIFF(s.end_date, s.start_date)) * 100),2) as sch_aug,
    round(((DATEDIFF(date_format('2017-09-30', '%Y-%m-%d'), s.start_date) / DATEDIFF(s.end_date, s.start_date)) * 100),2) as sch_sep,
    round(((DATEDIFF(date_format('2017-10-30', '%Y-%m-%d'), s.start_date) / DATEDIFF(s.end_date, s.start_date)) * 100),2) as sch_oct,
    round(((DATEDIFF(date_format('2017-11-30', '%Y-%m-%d'), s.start_date) / DATEDIFF(s.end_date, s.start_date)) * 100),2) as sch_nov,
    round(((DATEDIFF(date_format('2017-12-30', '%Y-%m-%d'), s.start_date) / DATEDIFF(s.end_date, s.start_date)) * 100),2) as sch_dec,
    round(((DATEDIFF(date_format('2018-01-30', '%Y-%m-%d'), s.start_date) / DATEDIFF(s.end_date, s.start_date)) * 100),2) as sch_jan,
    round(((DATEDIFF(date_format('2018-02-28', '%Y-%m-%d'), s.start_date) / DATEDIFF(s.end_date, s.start_date)) * 100),2) as sch_feb,
    round(((DATEDIFF(date_format('2018-03-30', '%Y-%m-%d'), s.start_date) / DATEDIFF(s.end_date, s.start_date)) * 100),2) as sch_mar,
    round(((DATEDIFF(date_format('2018-04-30', '%Y-%m-%d'), s.start_date) / DATEDIFF(s.end_date, s.start_date)) * 100),2) as sch_apr
  FROM tbl_scope_of_work w
  left join tbl_schedule_pre_site_survey s on s.milestone_id = w.id
  where w.property = 2
  group by w.id
  order by w.id asc
) a

possible duplicate of https://stackoverflow.com/questions/7674786/mysql-pivot-table and https://stackoverflow.com/questions/7674786/mysql-pivot-table — undefined_variable, Oct 13 '17 at 05:57
Refer this https://stackoverflow.com/questions/16913717/mysql-select-column-name-and-value-as-a-field — KMS, Oct 13 '17 at 06:05
This is a very strange query. You join the survey table to your work table, but then you group by the work table's ID. This is invalid SQL, as there is multiple survey data per work record. MySQL let's this slip and picks an arbitrary survey record per work record. This leaves you with rather random results. Then you use `DISTINCT` on the data, but it is already distinct, because your select list contains the grouped by column(s). — Thorsten Kettner, Oct 13 '17 at 07:12
You then order your result set, but as you select from it again, the `ORDER BY` clause gets void (data in a derived table is considered unordered, MySQL is free to completely ignore your `ORDER BY` clause). — Thorsten Kettner, Oct 13 '17 at 07:12
Please think about this. Does the query what you want it to do? It doesn't look like, but then I don't know your reqirement. — Thorsten Kettner, Oct 13 '17 at 07:15
There is more: You take a string `'2017-07-30'` and use `DATE_FORMAT` on it. `DATE_FORMAT` is a function to convert a date into a formatted string. So MySQL takes your string, silently converts it into a date using current settings, which is no problem as `'2017-07-30'` is unambiguous, takes this date and converts it to string, resulting in `'2017-07-30'` again. Then you use `DATEDIFF` on that string, but as this function wants a date, your string is again silently converted into date. An ANSI date literal looks like this: `DATE '2017-07-30'`. Hence: `datediff(date '2017-07-30', s.start_date)` — Thorsten Kettner, Oct 13 '17 at 07:24

score 0 · Accepted Answer · answered Oct 13 '17 at 06:00

0

You can query column by column and then union the results:

select name, value
from
(
  select 'data_july' as name, data_july as value, 1 as sortkey from mytable
  union all
  select 'data_august' as name, data_august as value, 2 as sortkey from mytable
  union all
  ...
) data
order by sortkey;

If you don't need to keep the sort order, then it's simply:

select 'data_july' as name, data_july as value from mytable
union all
select 'data_august' as name, data_august as value from mytable
union all
...

answered Oct 13 '17 at 06:00

Thorsten Kettner

89,309
7
49
73

its quite good but static..can you change to retrieve data dynamically – Ankit Agrawal Oct 13 '17 at 07:51
@Ankit Agrawal: When I wrote my answer, I expected to read data from a table. In this case we cannot write a query less static, as it is certain columns we read from. As the OP has added a query now and we see that the columns get generated, one could think of ways to re-write the whole query. But as you can see from my comments to the request, the query seems flawed anyway, so that would have to be fixed first. – Thorsten Kettner Oct 13 '17 at 08:20

Transpose columns into rows USING MYSQL

1 Answers1