Nitin Nitin - 5 months ago 7
SQL Question

coverting column to row using my sql

i have following mysql query:

SELECT
a.id, a.sbu_name, b.month,b.week, b.DateRange, b.planned
FROM
(SELECT
id, sbu_name
FROM
tbl_sbu) a
LEFT JOIN
(SELECT
sbu_id,
month,
WEEK(month) week,
CONCAT(DATE_FORMAT(DATE_ADD(month, INTERVAL (1 - DAYOFWEEK(month)) DAY), '%Y-%m-%e'), ' TO ', DATE_FORMAT(DATE_ADD(month, INTERVAL (7 - DAYOFWEEK(month)) DAY), '%Y-%m-%e')) AS DateRange,
SUM(amount) / 100000 AS planned
FROM
tbl_collection_planed
WHERE
DATE_FORMAT(month, '%Y-%m') = '2016-08'
GROUP BY DateRange) b ON b.sbu_id = a.id order by week asc


The output of above query is: image of current output

I want my output as follows:
image of wanted result

Answer

This is a table pivot question, if just for this case, you can try following query;)

SELECT 
    sbu_name,
    IF(DateRange = '2016-08-7 TO 2016-08-13', planned, '') AS `2016-08-7 TO 2016-08-13`,
    IF(DateRange = '2016-08-14 TO 2016-08-20', planned, '') AS `2016-08-14 TO 2016-08-20`,
    IF(DateRange = '2016-08-21 TO 2016-08-27', planned, '') AS `2016-08-21 TO 2016-08-27`,
    IF(DateRange = '2016-08-28 TO 2016-09-3', planned, '') AS `2016-08-28 TO 2016-09-3`
FROM (
    SELECT 
        a.id, a.sbu_name, b.month,b.week, b.DateRange, b.planned
    FROM
        (SELECT 
            id, sbu_name
        FROM
            tbl_sbu) a
            LEFT JOIN
        (SELECT 
            sbu_id,
                month,
                WEEK(month) week,
                CONCAT(DATE_FORMAT(DATE_ADD(month, INTERVAL (1 - DAYOFWEEK(month)) DAY), '%Y-%m-%e'), ' TO ', DATE_FORMAT(DATE_ADD(month, INTERVAL (7 - DAYOFWEEK(month)) DAY), '%Y-%m-%e')) AS DateRange,
                SUM(amount) / 100000 AS planned
        FROM
            tbl_collection_planed
        WHERE
            DATE_FORMAT(month, '%Y-%m') = '2016-08'
        GROUP BY DateRange) b ON b.sbu_id = a.id order by week asc
) t
GROUP BY sub_name

If this DateRange has dynamic values, you have to do this with a dynamic sql.

EDITED(dynamic sql)

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(DateRange = ''',
      CONCAT(DATE_FORMAT(DATE_ADD(month, INTERVAL (1 - DAYOFWEEK(month)) DAY), '%Y-%m-%e'), ' TO ', DATE_FORMAT(DATE_ADD(month, INTERVAL (7 - DAYOFWEEK(month)) DAY), '%Y-%m-%e')),
      ''', planned, '')) AS `',
      CONCAT(DATE_FORMAT(DATE_ADD(month, INTERVAL (1 - DAYOFWEEK(month)) DAY), '%Y-%m-%e'), ' TO ', DATE_FORMAT(DATE_ADD(month, INTERVAL (7 - DAYOFWEEK(month)) DAY), '%Y-%m-%e')), '`'
    )
  ) INTO @sql
FROM tbl_collection_planed
WHERE DATE_FORMAT(month, '%Y-%m') = '2016-08'
;
SET @sql = CONCAT('SELECT sbu_name, ', @sql, ' FROM (
    SELECT 
        a.id, a.sbu_name, b.month,b.week, b.DateRange, b.planned
    FROM
        (SELECT 
            id, sbu_name
        FROM
            tbl_sbu) a
            LEFT JOIN
        (SELECT 
            sbu_id,
                month,
                WEEK(month) week,
                CONCAT(DATE_FORMAT(DATE_ADD(month, INTERVAL (1 - DAYOFWEEK(month)) DAY), \'%Y-%m-%e\'), \' TO \', DATE_FORMAT(DATE_ADD(month, INTERVAL (7 - DAYOFWEEK(month)) DAY), \'%Y-%m-%e\')) AS DateRange,
                SUM(amount) / 100000 AS planned
        FROM
            tbl_collection_planed
        WHERE
            DATE_FORMAT(month, \'%Y-%m\') = \'2016-08\'
        GROUP BY DateRange) b ON b.sbu_id = a.id order by week asc
) t
GROUP BY sub_name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Comments