1

I have table structure like below

+--------------------------------------+
ID | cmpgn_pic | cmpgn_dt   | cmpgn_tg |
+--------------------------------------+
|1 | NUSNAFIF  | 2015-04-01 | 300000   |
|2 | PETERBSG  | 2015-04-01 | 30000    |
|3 | PTYONG    | 2015-04-02 | 700000   |
|4 | HMKWAN    | 2015-04-01 | 800000   |
|5 | NUSNAFIF  | 2015-04-05 | 400000   |
|6 | NUSNAFIF  | 2015-04-02 | 100000   |
|7 | HMKWAN    | 2015-04-10 | 75000    |
|9 | PTYONG    | 2015-04-01 | 10000    |
|10| PTYONG    | 2015-04-05 | 600000   |
|11| NUSNAFIF  | 2015-04-05 | 600000   |
|12| HMKWAN    | 2015-04-10 | 800000   |
+--------------------------------------+

how to write dynamic sql script to get result like below

+---------------------------------------------------------------------------------------------------------------------------------------------+
| cmpgn_pic | 2015-04-01 | 2015-04-02 | 2015-04-03 | 2015-04-04 | 2015-04-05 | 2015-04-06 | 2015-04-07 | 2015-04-08 | 2015-04-09 | 2015-04-10 |
+---------------------------------------------------------------------------------------------------------------------------------------------+
| HMKWAN    | 1          | 0          | 0          | 0          | 0          | 0          | 0          | 0          | 0          | 2          |
| NUSNAFIF  | 1          | 1          | 0          | 0          | 2          | 0          | 0          | 0          | 0          | 0          |
| PETEGSG   | 1          | 0          | 0          | 0          | 0          | 0          | 0          | 0          | 0          | 0          |
| PTYONG    | 1          | 1          | 0          | 0          | 1          | 0          | 0          | 0          | 0          | 0          |

I only able to write static mysql script as below

SELECT cmpgn_pic, COUNT(id)
FROM t_prebook
group by 1

But I don't know how to display dynamic date into my script. and I look for your help to solve my issue. Thanks you in advance.

A ツ
  • 1,267
  • 2
  • 9
  • 14
Chemplung
  • 11
  • 4

1 Answers1

0

I think the question is already answered a while ago, please refer to this answer.

MySQL pivot row into dynamic number of columns

Edit: MySQL does not yet have a PIVOT function. a possible workaround for you is to create a procedure that will append each unique date instances as a new column.

Community
  • 1
  • 1
Arzgethalm
  • 516
  • 5
  • 14
  • Here's a stored procedure to do the work of building the SELECT: http://mysql.rjweb.org/doc.php/pivot – Rick James Mar 22 '15 at 21:01
  • Hi Arzgethalm, I try to create sqlfidlle http://sqlfiddle.com/#!9/d292e/1 , but when I try to run the query it was return me an error. please help me to enhance those script. – Chemplung Mar 24 '15 at 03:40
  • Hi Chemplung please check the modifications made to make it work. http://sqlfiddle.com/#!9/d292e/8/4 – Arzgethalm Mar 24 '15 at 14:55