mysql sql行转列动态列名

最新推荐文章于 2024-07-22 03:17:47 发布

grace.free

最新推荐文章于 2024-07-22 03:17:47 发布

阅读量3.7k

点赞数

分类专栏： JAVA mysql

本文链接：https://blog.csdn.net/qq_36291682/article/details/81458065

版权

JAVA 同时被 2 个专栏收录

126 篇文章 3 订阅

订阅专栏

mysql

31 篇文章 0 订阅

订阅专栏

DROP TABLE IF EXISTS `tt`;
CREATE TABLE `tt` (
  `id` int(11) NOT NULL,
  `filed_name` varchar(30) DEFAULT NULL,
  `val` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tt
-- ----------------------------
INSERT INTO `tt` VALUES ('1', 'A', '11');
INSERT INTO `tt` VALUES ('1', 'B', '22');
INSERT INTO `tt` VALUES ('1', 'C', '33');
INSERT INTO `tt` VALUES ('2', 'A', '44');
INSERT INTO `tt` VALUES ('2', 'B', '55');
INSERT INTO `tt` VALUES ('2', 'C', '66');

select 
id,
max(A) A,
max(B) B,
max(C) C
from(
	select id,
				case  filed_name  when 'A'  THEN val else  0 end A,
				case  filed_name  when 'B'  THEN val else  0 end B,
				case  filed_name  when 'C'  THEN val else  0 end C
	FROM tt
) t2
group by id

select id,
			MAX(case filed_name  when 'A' THEN  val  ELSE 0 END ) AS 'A',
			MAX(case filed_name  when 'B' THEN  val  ELSE 0 END ) AS 'B',
			MAX(case filed_name  when 'C' THEN  val  ELSE 0 END ) AS 'C'
	FROM tt
 group by id

select id ,(
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(case filed_name  when ''',t.filed_name,''' THEN  val  ELSE 0 END ) AS ''',t.filed_name, ''''
    )
  )
FROM tt t
)  FROM tt

结果：