mysql 行列转换怎么写？

set @i =0

create table 新表

AS

select @i:=@i+1 as NO,NO1,NO2,NO3,MYA1 as MM

from(

select NO1,NO2,'MYA1 ' as NO3,MYA1 from 表

union all

select NO1,NO2,'MYA2 ',MYA2 from 表

union all

select NO1,NO2,'MYA3 ',MYA3 from 表

union all

select NO1,NO2,'MYA4 ',MYA4 from 表

union all

select NO1,NO2,'MYA5 ',MYA5 from 表

union all

select NO1,NO2,'MYA6 ',MYA6 from 表

)a order by NO1,NO2

select * from 新表

CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END 在第一个方案的返回结果中， value=compare-value。而第二个方案的返回结果是第一种情况的真实结果。如果没有匹配的结果值，则返回结果为ELSE后的结果，如果没有ELSE 部分，则返回值为 NULL。

mysql>SELECT CASE 1 WHEN 1 THEN 'one'

->WHEN 2 THEN 'two' ELSE 'more' END

->'one'

mysql>SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END

->'true'

mysql>SELECT CASE BINARY 'B'

->WHEN 'a' THEN 1 WHEN 'b' THEN 2 END

->NULL

你用case when 函数试试。

打字不易，如满意，望采纳。

#这句试试看

select m.users,m.row_value '名字',a.row_value '年龄',d.row_value '住址' from

(select users ,row_value from test where row_key='名字') m,

(select users,row_value from test where row_key='年龄') a,

(select users ,row_value from test where row_key='住址') d

where m.users=a.users and m.users=d.users

#下面这个是创建表和插入数据

DROP TABLE IF EXISTS `test`

CREATE TABLE `test` (

`id` int(4) NOT NULL,

`users` char(2) DEFAULT NULL,

`row_key` varchar(20) DEFAULT NULL,

`row_value` varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- ----------------------------

-- Records of test

-- ----------------------------

INSERT INTO `test` VALUES ('1', '甲', '名字', '张三')

INSERT INTO `test` VALUES ('2', '甲', '年龄', '30')

INSERT INTO `test` VALUES ('3', '甲', '住址', '上海')

INSERT INTO `test` VALUES ('4', '已', '名字', '张三2')

INSERT INTO `test` VALUES ('5', '已', '年龄', '40')

INSERT INTO `test` VALUES ('6', '已', '住址', '上海2')

INSERT INTO `test` VALUES ('7', '丙', '名字', '张三1')

INSERT INTO `test` VALUES ('8', '丙', '年龄', '50')

INSERT INTO `test` VALUES ('9', '丙', '住址', '上海1')

---