数据库实现列值合并为行

转载自：http://hi.baidu.com/wangzhiqing999/blog/item/40982215996caa62cb80c46e.html

 

CREATE TABLE TestTitle (
  name   VARCHAR(10),
  seq    INT,
  title  VARCHAR(10)
);

 

INSERT INTO TestTitle VALUES ('张三', 3, '程序员');
INSERT INTO TestTitle VALUES ('张三', 1, '系统管理员');
INSERT INTO TestTitle VALUES ('张三', 2, '网络管理员');

INSERT INTO TestTitle VALUES ('李四', 2, '项目经理');
INSERT INTO TestTitle VALUES ('李四', 1, '系统分析员');

 

这里要求 是， 合并后， 字符的顺序，是按照  seq  来排序的

也就是最后的结果，希望是

李四     系统分析员,项目经理

张三     系统管理员,网络管理员,程序员

 

 

 

对于MySQL使用 GROUP_CONCAT 函数 的方式进行处理

 首先是 不排序的情况下，默认的查询结果：

 

 

mysql> SELECT
    ->   name,
    ->   GROUP_CONCAT(title) AS allTitle
    -> FROM
    ->   TestTitle
    -> GROUP BY
    ->   name;
+------+------------------------------+
| name | allTitle                     |
+------+------------------------------+
| 李四 | 项目经理,系统分析员          |
| 张三 | 程序员,系统管理员,网络管理员 |
+------+------------------------------+
2 rows in set (0.00 sec)

 

然后是按照 seq   的顺序进行排列的查询结果：

mysql> SELECT
    ->   name,
    ->   GROUP_CONCAT(title) AS allTitle
    -> FROM
    ->   (
    ->   SELECT
    ->     name,
    ->     title
    ->   FROM
    ->     TestTitle
    ->   ORDER BY
    ->     seq
    ->   ) as subQuery
    -> GROUP BY
    ->   name;
+------+------------------------------+
| name | allTitle                     |
+------+------------------------------+
| 李四 | 系统分析员,项目经理          |
| 张三 | 系统管理员,网络管理员,程序员 |
+------+------------------------------+
2 rows in set (0.00 sec)

 

 

 

对于Oracle使用 WMSYS.WM_CONCAT 函数 的方式进行处理

 

首先是 不排序的情况下，默认的查询结果：

SQL>
SQL> SELECT
  2    name,
  3    WMSYS.WM_CONCAT(title) AS allTitle
  4  FROM
  5    TestTitle
  6  GROUP BY
  7    name;

NAME
----------
ALLTITLE
-------------------------------------------
李四
项目经理,系统分析员

张三
程序员,系统管理员,网络管理员

 

然后是按照 seq   的顺序进行排列的查询结果：

SQL> with myCTE AS
  2  (
  3    SELECT
  4      name,
  5      seq,
  6      WMSYS.WM_CONCAT(title)
  7          OVER(PARTITION BY  name   ORDER BY   seq) AS allTitle
  8    FROM
  9      TestTitle
 10  )
 11  SELECT
 12    name, allTitle
 13  FROM
 14    myCTE
 15  WHERE
 16    NOT EXISTS(
 17      SELECT 1
 18      FROM myCTE sub
 19      WHERE myCTE.name = sub.name
 20        AND myCTE.seq < sub.seq
 21    );

NAME
----------
ALLTITLE
--------------------------------------------------------------
李四
系统分析员,项目经理

张三
系统管理员,网络管理员,程序员

 

select 
from ipms_case,(
		select case_id  
		       ,ltrim(sys_connect_by_path(name, '$@$'), '$@$') as name   
			   ,ltrim(sys_connect_by_path(SERVER_HOSTING_ADDRESS, '$@$'), '$@$') as SERVER_HOSTING_ADDRESS   
			   ,ltrim(sys_connect_by_path(ICP_REC_NUM , '$@$'), '$@$') as ICP_REC_NUM  
			   ,ltrim(sys_connect_by_path(ICP_REG_ADDR, '$@$'), '$@$') as ICP_REG_ADDR
			   ,ltrim(sys_connect_by_path(HOME_WEB_URL, '$@$'), '$@$') as HOME_WEB_URL   
			   ,ltrim(sys_connect_by_path(IP_ADDR, '$@$'), '$@$') as IP_ADDR   
		from  
		    (
				select  asso.case_id as case_id
				,site.NAME as name
				,site.SERVER_HOSTING_ADDRESS as SERVER_HOSTING_ADDRESS
				,site.ICP_REC_NUM as ICP_REC_NUM 
				,site.ICP_REG_ADDR as ICP_REG_ADDR
				,site.HOME_WEB_URL as HOME_WEB_URL
				,site.IP_ADDR as IP_ADDR
				,(row_number()  over(partition by asso.case_id order by asso.case_id)  ) numid    
				from IPMS_CASE_SITE_ASSO asso,ipms_website site
				where asso.SITE_ID=site.id 
			)
		 
		   start with numid = 1    
		   connect by numid - 1 = prior numid    and case_id = prior case_id   
)