mysql 将列转换为行

yhived7q  于 2023-06-28  发布在  Mysql
关注(0)|答案(1)|浏览(102)

我有问题mysqlqueris动态转换成行列,
我有一张这样的table:
| 纳马布洛克|塔洪塔纳姆|卢阿斯|Jumlah_pokok| Jumlah_pokok |
| - -----|- -----|- -----|- -----| ------------ |
| Blok 1|二千年|一百|三百| 300 |
| Blok 2|一九九八年|三百|五百| 500 |
然后我想创建这样的报告:
| Blok 1| Blok 2| Blok 2 |
| - -----|- -----| ------------ |
| 二千年|一九九八年| 1998 |
| 一百|三百| 300 |
| 三百|五百| 500 |
帮我解决查询mysql,谢谢。

vyswwuz2

vyswwuz21#

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;

相关问题