2 Added the usual link
source | link

If you have a known number of columns, then you can use a static version similar to the other answer. But if you have an unknown number then you can use a prepared statementprepared statement similar to this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when kode = ''',
      kode,
      ''' then jum else 0 end) AS ',
      kode
    )
  ) INTO @sql
FROM yourtable;


SET @sql = CONCAT('SELECT name, ', @sql, ', sum(jum) as `count`
                  FROM yourtable 
                  GROUP BY name');

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

See SQL Fiddle with demo

Result is the same:

| NAME | KODE1 | KODE2 | COUNT |
--------------------------------
| aman |     2 |     1 |     3 |
| amir |     0 |     4 |     4 |
| jhon |     4 |     0 |     4 |

If you have a known number of columns, then you can use a static version similar to the other answer. But if you have an unknown number then you can use a prepared statement similar to this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when kode = ''',
      kode,
      ''' then jum else 0 end) AS ',
      kode
    )
  ) INTO @sql
FROM yourtable;


SET @sql = CONCAT('SELECT name, ', @sql, ', sum(jum) as `count`
                  FROM yourtable 
                  GROUP BY name');

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

See SQL Fiddle with demo

Result is the same:

| NAME | KODE1 | KODE2 | COUNT |
--------------------------------
| aman |     2 |     1 |     3 |
| amir |     0 |     4 |     4 |
| jhon |     4 |     0 |     4 |

If you have a known number of columns, then you can use a static version similar to the other answer. But if you have an unknown number then you can use a prepared statement similar to this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when kode = ''',
      kode,
      ''' then jum else 0 end) AS ',
      kode
    )
  ) INTO @sql
FROM yourtable;


SET @sql = CONCAT('SELECT name, ', @sql, ', sum(jum) as `count`
                  FROM yourtable 
                  GROUP BY name');

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

See SQL Fiddle with demo

Result is the same:

| NAME | KODE1 | KODE2 | COUNT |
--------------------------------
| aman |     2 |     1 |     3 |
| amir |     0 |     4 |     4 |
| jhon |     4 |     0 |     4 |
1
source | link

If you have a known number of columns, then you can use a static version similar to the other answer. But if you have an unknown number then you can use a prepared statement similar to this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when kode = ''',
      kode,
      ''' then jum else 0 end) AS ',
      kode
    )
  ) INTO @sql
FROM yourtable;


SET @sql = CONCAT('SELECT name, ', @sql, ', sum(jum) as `count`
                  FROM yourtable 
                  GROUP BY name');

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

See SQL Fiddle with demo

Result is the same:

| NAME | KODE1 | KODE2 | COUNT |
--------------------------------
| aman |     2 |     1 |     3 |
| amir |     0 |     4 |     4 |
| jhon |     4 |     0 |     4 |