mysql-行轉列、列轉行

group_concat()，函數說明

    手冊上說明:該函數傳回帶有來自一個組的連接配接的非NULL值的字元串結果；

    通俗點了解，其實是這樣的：group_concat()會計算哪些行屬于同一組，将屬于同一組的列顯示出來。要傳回哪些列，由函數參數(就是字段名)決定。分組必須有個标準，就是根據group by指定的列進行分組。

1、行轉列

-- 不轉sql語句  
SELECT t2.HIERARCHY_ID FROM t_hierarchy t2 WHERE t2.`DEPARTMENT_PID` = (SELECT t1.DEPARTMENT_ID FROM t_staff t1 WHERE t1.id = 3) AND t2.`TYPE`=1030 AND STATUS=1  
  
-- 将行轉列以","隔開  
SELECT GROUP_CONCAT(t2.HIERARCHY_ID SEPARATOR ',') FROM t_hierarchy t2 WHERE t2.`DEPARTMENT_PID` = (SELECT t1.DEPARTMENT_ID FROM t_staff t1 WHERE t1.id = 3) AND t2.`TYPE`=1030 AND STATUS=1;  
  
-- 将行轉列以","隔開,如果填上其他符号則是以","+符号隔開  
SELECT GROUP_CONCAT(t2.HIERARCHY_ID,'') FROM t_hierarchy t2 WHERE t2.`DEPARTMENT_PID` = (SELECT t1.DEPARTMENT_ID FROM t_staff t1 WHERE t1.id = 3) AND t2.`TYPE`=1030 AND STATUS=1;  
  
-- 将行轉列以","隔開  
SELECT GROUP_CONCAT(t2.HIERARCHY_ID) FROM t_hierarchy t2 WHERE t2.`DEPARTMENT_PID` = (SELECT t1.DEPARTMENT_ID FROM t_staff t1 WHERE t1.id = 3) AND t2.`TYPE`=1030 AND STATUS=1;
           

(不轉輸出)

(行轉列後輸出)

2、行轉列列傳行更具體例子

   1、檢視資料

SELECT  * FROM tabName ;
           

   2、列轉行統計資料

SELECT DATE ,
	MAX(CASE NAME WHEN '小說' THEN Scount ELSE 0 END ) 小說,
	MAX(CASE NAME WHEN '微信' THEN Scount ELSE 0 END ) 微信 
FROM TabName  
GROUP BY DATE
           

3、行轉列

SELECT DATE,GROUP_CONCAT(NAME) FROM tabname GROUP BY DATE
           

4、

行轉列統計資料

SELECT DATE, GROUP_CONCAT(NAME,'總量:',Scount) AS b_str FROM tabName GROUP BY DATE
           

SELECT DATE,NAME, GROUP_CONCAT(NAME,'總量:',Scount) AS b_str FROM   TabName GROUP BY DATE ,NAME