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,MILLER
20 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 行)

*/

  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值