MySQL Pivot Table

[ArunaTelshan]

http://onlybluefeet.com/2015/01/18/how-to-rotate-rows-into-columns-in-mysql/
meke pallehama thiyena eka

 

[ranga_uoc]

SET @sql = NULL;

SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(CASE WHEN MonthYear = ''',
MonthYear,
''' THEN amt ELSE 0 END) AS ',
MonthYear
)
) INTO @sql
FROM (
SELECT
stu_id,
DATE_FORMAT(date, '%b_%y') MonthYear,
amt
FROM fees
WHERE date BETWEEN CAST('2017-01-01' AS DATE) AND CAST('2018-07-31' AS DATE)
ORDER BY date
) src;

SET @sql = CONCAT("SELECT stu_id, ", @sql, "
FROM (
SELECT
stu_id,
DATE_FORMAT(date, '%b_%y') MonthYear,
amt
FROM fees
WHERE date BETWEEN CAST('2013-01-01' AS DATE) AND CAST('2018-07-31' AS DATE)
ORDER BY date
) src
GROUP BY stu_id");

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

 

[ArunaTelshan]

SET @sql = NULL;

SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(CASE WHEN MonthYear = ''',
MonthYear,
''' THEN amt ELSE 0 END) AS ',
MonthYear
)
) INTO @sql
FROM (
SELECT
stu_id,
DATE_FORMAT(date, '%b_%y') MonthYear,
amt
FROM fees
WHERE date BETWEEN CAST('2017-01-01' AS DATE) AND CAST('2018-07-31' AS DATE)
ORDER BY date
) src;

SET @sql = CONCAT("SELECT stu_id, ", @sql, "
FROM (
SELECT
stu_id,
DATE_FORMAT(date, '%b_%y') MonthYear,
amt
FROM fees
WHERE date BETWEEN CAST('2013-01-01' AS DATE) AND CAST('2018-07-31' AS DATE)
ORDER BY date
) src
GROUP BY stu_id");

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

man me karapu widiya harida