【SQL 编程你也行】SQL行转列

比如有 A B 表
A 表
ID,NAME
1,A
2,B
3,C

B 表
FK_ID,TYPE,VALUE1,VALUE2
1，cpu，100,90
1，mem，90,89
2，cpu，20,22
2，mem，100,99
3，cpu，90,60
3，mem，20,20

求这样的结果
A_ID,A_NAME,A_CPU_RECORD,A_MEM_RECORD
1,A,'100/90',"90/89"
2,B,'20/22',"100/99"

3,C,'90/60',"20/20"

代码如下：

 

SELECT a.id AS a_id,
       a.name AS a_name,
       MAX(CASE WHEN TYPE='cpu' THEN CONCAT(b.value1,'/',b.value2) END) AS A_CPU_RECORD,
       MAX(CASE WHEN TYPE='mem' THEN CONCAT(b.value1,'/',b.value2) END) AS A_MEM_RECORD
              
FROM 
(
	SELECT 1 AS ID,'A' AS NAME UNION ALL
	SELECT 2,'B' UNION ALL
	SELECT 3,'C'
)a
INNER JOIN 
(
	SELECT 1 FK_ID,'cpu' TYPE,'100' VALUE1,'90' VALUE2 UNION ALL
	SELECT 1,'mem','90','89' UNION ALL
	SELECT 2,'cpu','20','22' UNION ALL
	SELECT 2,'mem','100','99' UNION ALL
	SELECT 3,'cpu','90','60'  UNION ALL
	SELECT 3,'mem','20','20'
)b
ON a.id = b.fk_id
GROUP BY a.id,a.name

查询结果：

 

 

转载于:https://www.cnblogs.com/momogua/p/8304406.html