MySQL single table static and dynamic pivot
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 |
To convert row to column you need to use CASE
statement. And to get count for individual kode
you need to use SUM
function like this one:
SELECT NAME,
SUM(CASE kode WHEN 'kode1' THEN jum ELSE 0 END) AS kode1
,SUM(CASE kode WHEN 'kode2' THEN jum ELSE 0 END) AS kode2
,SUM(jum) AS `Count`
FROM Table1
GROUP BY Name
See this SQLFiddle
This is a form of a "pivot", you should use that search term to find other options.
You can try something like this:
select
name
, sum(case when kode = 'kode1' then jum else 0 end) as 'kode1'
, sum(case when kode = 'kode2' then jum else 0 end) as 'kode2'
, sum(jum) as count
from foo
group by name;
(assuming your example view has an error for jhon
)
Example:
mysql> select * from foo;
+------+-------+------+
| name | kode | jum |
+------+-------+------+
| aman | kode1 | 2 |
| aman | kode2 | 1 |
| jhon | kode1 | 4 |
| amir | kode2 | 4 |
+------+-------+------+
4 rows in set (0.00 sec)
mysql> select
-> name
-> , sum(case when kode = 'kode1' then jum else 0 end) as 'kode1'
-> , sum(case when kode = 'kode2' then jum else 0 end) as 'kode2'
-> , sum(jum) as count
-> from foo
-> group by name;
+------+-------+-------+-------+
| name | kode1 | kode2 | count |
+------+-------+-------+-------+
| aman | 2 | 1 | 3 |
| amir | 0 | 4 | 4 |
| jhon | 4 | 0 | 4 |
+------+-------+-------+-------+
3 rows in set (0.00 sec)