oracle和sql server 把一列转成一行显示

Oracle一列的多行数据拼成一行显示字符

oracle 提供了两个函数WMSYS.WM_CONCAT 和 ListAgg函数。
www.2cto.com
先介绍：WMSYS.WM_CONCAT
例：
id name
1 aa
2 bb
3 cc
要的结果是”aa,bb,cc”

select WMSYS.WM_CONCAT(a.name) from user a
这样的话，查询出的结果：”aa,bb,cc”
www.2cto.com
分隔符如果不需要用英文的逗号，需要改成别的符号比如分号的，可以用下面的方法替换下：
select replace(WMSYS.WM_CONCAT(a.name),’,’,’；’) from user a

结果：”aa;bb;cc”

ListAgg函数
listagg函数的语法结构如下：
LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]
listagg虽然是聚合函数，但可以提供分析功能（比如可选的OVER()子句）。使用listagg中，下列中的元素是必须的： www.2cto.com
•需要聚合的列或者表达式
•WITH GROUP 关键词
•分组中的ORDER BY子句
例子：
DEPTNO ENAME

---

   10 CLARK   10 KING   10 MILLER   20 ADAMS   20 FORD   20 JONES

按照DEPTNO字段分组，对结果集进行字符串聚合，结果如下：
DEPTNO AGGREGATED_ENAMES

---

10 CLARK,KING,MILLER20 ADAMS,FORD,JONES

SQL:
SELECT deptno,LISTAGG(ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees FROM emp GROUP BY deptno;

sqlserver:

学习到的内容：stuff函数的应用

问题：

有一个用户表(姓名,编号,爱好)
user(name,id,hobby),
里面的数据有：
张三 001 篮球
张三 001 电影
李四 002 足球
王五 003 上网
李四 002 看美女
现在要求写一SQL语句，使查出结果为：
张三 001 篮球，电影
李四 002 足球，看美女
王五 003 上网

解决方案：

方案一：

SQL code
IF OBJECT_ID(‘USER’) IS NOT NULL DROP TABLE [USER]
GO
CREATE TABLE [USER](
NAME VARCHAR(10)
,ID VARCHAR(5)
,HOBBY VARCHAR(10)
)
INSERT INTO [USER]
SELECT ‘张三’,’001’,’篮球’ UNION ALL
SELECT ‘张三’,’001’,’电影’ UNION ALL
SELECT ‘李四’,’002’,’足球’ UNION ALL
SELECT ‘王五’,’003’,’上网’ UNION ALL
SELECT ‘李四’,’002’,’看美女’

SELECT NAME,ID
,STUFF((SELECT ‘,’+HOBBY FROM [USER] T2 WHERE T2.NAME=T1.NAME FOR XML PATH(”)),1,1,”)
FROM [USER] T1
GROUP BY NAME,ID
/*
NAME ID

---

李四 002 足球,看美女
王五 003 上网
张三 001 篮球,电影
*/
方案二：
SQL code

–SQL2000

–1. 创建处理函数
create table tb(name varchar(10),ID int,hobby varchar(20))
insert into tb
select ‘张三’,’001’,’篮球’
union all
select ‘张三’,’001’,’电影’
union all
select ‘李四’,’002’,’足球’
union all
select ‘王五’,’003’,’上网’
union all
select ‘李四’,’002’,’看美女’

go

CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ”
SELECT @r = @r + ‘,’ + hobby FROM tb WHERE id=@id
RETURN STUFF(@r, 1, 1, ”)
END
GO

– 调用函数
SELECt name, hobby = dbo.f_str(id) FROM tb GROUP BY name,id

drop table tb
drop function dbo.f_str
/*

（所影响的行数为 5 行）

name hobby
李四 足球,看美女
王五 上网
张三 篮球,电影

（所影响的行数为 3 行）

*/