Mysql Convert Column to row

Question

i have problem mysql queris dynamic convert columns into rows,

I have a table like this :

id	Nama Blok	Tahun_tanam	Luas	Jumlah_pokok
1	Blok 1	2000	100	300
2	Blok 2	1998	300	500

And then I want to create report like this :

Blok	Blok 1	Blok 2
Tahun Tanam	2000	1998
Luas	100	300
Jumlah Pokok	300	500

help me solution query mysql, thanks.....

I think the term you are looking for is [Pivot Table and here is a good Q & A on that for you to start your research](https://stackoverflow.com/questions/7674786) But how you would create the `Blok` column from that table I have no idea, my magicians credentials have lapsed — RiggsFolly, Jun 26 '23 at 08:10
Yes He must be looking for pivot with case expression and max function — Hasan Raza, Jun 26 '23 at 09:17
Does this answer your question? [How can I return pivot table output in MySQL?](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql) — Miles Rout, Jun 29 '23 at 06:56

score 0 · Answer 1 · answered Jun 26 '23 at 09:18

    SELECT
   'Blok' AS Attribute,
    MAX(CASE WHEN `Nama Blok` = 'Blok 1' THEN `Nama Blok` END) AS `Blok 1`,
    MAX(CASE WHEN `Nama Blok` = 'Blok 2' THEN `Nama Blok` END) AS `Blok 2`
   FROM your_table
   UNION
   SELECT
   'Tahun Tanam' AS Attribute,
   MAX(CASE WHEN `Nama Blok` = 'Blok 1' THEN `Tahun_tanam` END) AS `Blok 1`,
   MAX(CASE WHEN `Nama Blok` = 'Blok 2' THEN `Tahun_tanam` END) AS `Blok 2`
   FROM your_table
   UNION
   SELECT
  'Luas' AS Attribute,
  MAX(CASE WHEN `Nama Blok` = 'Blok 1' THEN `Luas` END) AS `Blok 1`,
  MAX(CASE WHEN `Nama Blok` = 'Blok 2' THEN `Luas` END) AS `Blok 2`
   FROM your_table
   UNION
   SELECT
  'Jumlah Pokok' AS Attribute,
   MAX(CASE WHEN `Nama Blok` = 'Blok 1' THEN `Jumlah_pokok` END) AS `Blok 1`,
  MAX(CASE WHEN `Nama Blok` = 'Blok 2' THEN `Jumlah_pokok` END) AS `Blok 2`
  FROM your_table;

Mysql Convert Column to row

1 Answers1