How can i get Query From Table Using Select in Mysql

Question

I have Table as name jual

 Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id_jual    | int(11)     | NO   | PRI | NULL    |       |
| tanggal    | date        | YES  |     | NULL    |       |
| jumlah     | int(4)      | NO   |     | NULL    |       |
| id_obat    | int(11)     | YES  | MUL | NULL    |       |
| id_periksa | int(11)     | YES  | MUL | NULL    |       |
| pemakaian  | varchar(30) | YES  |     | NULL    |       |

When i executed select * from jual " the result here "

 id_jual | tanggal    | jumlah | id_obat | id_periksa | pemakaian |
+---------+------------+--------+---------+------------+-----------+
|       1 | 2014-10-29 |     20 |       1 |          1 | 3x1       |
|       2 | 2014-10-29 |     10 |       5 |          1 | 2x1       |
|       5 | 2014-10-29 |     20 |       8 |          2 | 2x1       |
|       6 | 2014-10-29 |     20 |       1 |          3 | 3x1       |
|      10 | 2014-10-29 |     13 |       5 |          4 | 2x1       |
|      11 | 2014-10-29 |     10 |       8 |          4 | 2x1       |
|      12 | 2014-10-29 |     30 |       1 |          5 | 2x1       |
|      15 | 2014-10-29 |     30 |       3 |          6 | 21        |
|      16 | 2014-10-29 |     20 |       8 |          6 | 3x1       |
|      18 | 2014-10-29 |     30 |       3 |          7 | 1x2       |
|      22 | 2014-10-30 |     20 |       3 |          9 | 1x2       |
|      39 | 2014-10-30 |     10 |       3 |         18 | 1x1       |
|      43 | 2014-10-31 |     15 |       3 |         20 | 2x1       |
|      46 | 2014-10-31 |     10 |       5 |         21 | 2x1       |
|      47 | 2014-11-01 |     20 |       6 |         22 | 1x1       |
|      50 | 2014-11-01 |      7 |       6 |         23 | 1x1       |

how can i get result view like this :

id_obat   |                         tanggal   
+---------+------------+----------+----------+----------+-----------+
            2014-10-29 |2014-10-30|2014-10-31|2014-11-01|2014-11-02
+---------+------------+----------+----------+----------+-----------+
|  1      | 20.20.30   |          |          |          |            |
|  3      | 30.30      | 20.10    | 15       |          |            |
|  5      | 10.13      |          | 10.      |          |            |
|  6      |            |          | 20.7     |          |            |
|  8      | 20.10.20   |          |          |          |            |
+---------+------------+----------+----------+----------+------------+

please help me with my problem.....!!!!

What you want is a pivot table, and generally you need a reporting or spreadsheet software, or more advanced database such as MS SQL. Here is a similar question: http://stackoverflow.com/questions/7674786/mysql-pivot-table — Sheepy, Nov 24 '14 at 06:10

score 0 · Answer 1 · answered Nov 24 '14 at 07:01

You can do this with a dynamic pivot statement like this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(tanggal = ''',
      tanggal,
      ''', val, NULL)) AS ''',
      tanggal,
      ''''
    )
  ) INTO @sql
FROM jual;

SET @sql = CONCAT('
       SELECT id_obat, ', @sql, ' 
       FROM (
         SELECT 
           tanggal, 
           id_obat, 
           GROUP_CONCAT(jumlah SEPARATOR ''.'') AS val 
         FROM jual
         GROUP BY tanggal, id_obat
         ORDER BY id_jual) AS DERIVED_TABLE
       GROUP BY id_obat;');

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

See this sample SQL Fiddle

The results won't match your sample output exactly, but I think that is because the sample output is incorrect as the value 20.7 should be under 2014-11-01 and not 2014-10-31 like it is in the sample.

yes it's true, but how can i create view from pivot statement above ? — user1191933, Nov 25 '14 at 11:08
@user1191933 If you want it to be dynamic I don't think you can use a view at all as views can't contain dynamic code as I recall. — jpw, Nov 25 '14 at 11:23

score 0 · Answer 2 · answered Nov 24 '14 at 07:19

SELECT tangaal, id_obat, GROUP_CONCAT( jumlah ) AS jumlah 
FROM  `jual` 
GROUP BY tangaal, id_obat

this will give output as

tangaal     |   id_obat     |   jumlah      |
------------+---------------+---------------+
2014-10-29  |   1           |   20,30,20    |
------------+---------------+---------------+
2014-10-29  |   3           |   30,30       |
------------+---------------+---------------+
..............

How can i get Query From Table Using Select in Mysql

2 Answers2