Dynamically Field ( sum case )

Question

I have query which I would like to make dynamically, as TH2014 TH2015, ....

Below is my query

SELECT tgr.no_rptka
     , tgr.get_name_jabatan
     , SUM(CASE WHEN tgr.get_year_rptka = YEAR(tmr.date_rptka) THEN tgr.jumlah END) TH2014
     , SUM(CASE WHEN tgr.get_year_rptka = YEAR(tmr.date_rptka)+1 THEN tgr.jumlah END) TH2015 
  FROM tbl_master_rptka tmr
     , tbl_get_rptka tgr
 WHERE tmr.no_rptka = tgr.no_rptka
 GROUP 
    BY tgr.get_name_jabatan
     , tgr.no_rptka 
 ORDER 
    BY tgr.no_rptka,id_get ASC

How can I do this?

In general, issues of data presentation are best left to the presentation layer. — Strawberry, Feb 01 '16 at 10:31
Possible duplicate of [MySQL pivot row into dynamic number of columns](http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) — Shadow, Feb 01 '16 at 11:11

score 0 · Answer 1 · answered Feb 02 '16 at 08:20

    SET @SQL = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when (tgr.get_year_rptka) = ''',
      dt,
      ''' then tgr.jumlah else 0 end) AS `',
      dt, '`'
    )
  ) INTO @SQL
FROM
(
  SELECT get_year_rptka AS dt
  FROM tbl_get_rptka tgr
  ORDER BY tgr.get_year_rptka
) d;

SET @SQL = CONCAT('SELECT tgr.no_rptka,tgr.get_name_jabatan, ', @SQL, ' 
            from tbl_master_rptka tmr
            inner join tbl_get_rptka tgr
              on tmr.no_rptka=tgr.no_rptka

            GROUP BY   tgr.get_name_jabatan, tgr.no_rptka 
            ORDER by   tgr.no_rptka,tgr.get_name_jabatan,   id_get ASC;');

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

But, i have trouble to combine in Zeos Component in delphi, so would u like to help me? — user3409576, Feb 02 '16 at 08:20

Dynamically Field ( sum case )

1 Answers1