sqlserver中 多条数据合并成一条数据 (stuff 与 for xml path 连用)

SQL 列转行,即多行合并成一条

 

需求:按照分组,将多条记录内容合并成一条,效果如下:

数据库示例:

复制代码
CREATE TABLE [t2]([NID] [bigint] NULL,[district] [nvarchar](255) NULL,[town] [nvarchar](255) NULL);
insert into t2 values(1,'淮上区','曹老集镇');
insert into t2 values(2,'淮上区','淮滨街道');
insert into t2 values(3,'淮上区','梅桥乡');
insert into t2 values(4,'淮上区','吴小街镇');
insert into t2 values(5,'淮上区','小蚌埠镇');
insert into t2 values(1,'光明新区','公明街道');
insert into t2 values(2,'光明新区','光明街道');
insert into t2 values(1,'吉利区','大庆路街道');
insert into t2 values(2,'吉利区','吉利乡');
复制代码

 

根据不同的SQL版本,可以有以下方法:

一、SQL 2000 不支持FOR XML,不支持CONCAT。只能写自定义函数。

复制代码
CREATE FUNCTION dbo.townconcat(@district nvarchar(255)) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @str varchar(8000) 
    SET @str = '' 
    SELECT @str = @str + ',' + town FROM t2 WHERE district=@district 
    RETURN STUFF(@str, 1, 1, '') 
END 
GO 
-- 调用函数 
SELECt district, town = dbo.townconcat(district) FROM t2 GROUP BY district 

drop function dbo.townconcat
go
复制代码

二、SQL 2012 支持 concat,2000版本自定义函数的基础上可少量优化

复制代码
--将2000版中的
SELECT @str = @str + ',' + town FROM t2 WHERE district=@district
--变成
SELECT @str = concat(@str,',',town) FROM t2 WHERE district=@district 

其他代码不变
复制代码


三、SQL2005支持for xml,可以大量简化

select distinct a.district,
(SELECT town+','FROM t2 where district=a.district FOR XML PATH(''))as towns 
from t2 a

 

以上三种方法都可以实现同样的效果。效果第一段的需求中的效果。

 

四、分析:
以上3种方法各有优劣,个人喜欢for xml的方式,因为够简单,一条select解决,可以直接适用于各视图中。

核心的代码是:

SELECT town+','FROM t2  FOR XML PATH('')

上面的代码得到的结果为:

注:
1、上图中的列名是自动生成的,不可以通过as 来命名。
2、我们不可以select多列,比如SELECT district,town+',' as tt FROM t2  FOR XML PATH('')。

如果加上,并不会报错,但效果可能不是我们想要的,如下图:

 

那我们如何根据关键字段来分组呢,我们可以把(select ..FOR XML..)作为子查询生成字段,看下图:

得到上图就明白了吧,直接用distinct就可以了,见三。

 

---------------------------------------------

附录

两种sql 方式:

第一种:  select distinct a.type,
(SELECT title+','FROM wk_CarInfo where type=a.type FOR XML PATH(''))as towns 
from wk_CarInfo a

------------------
第二种,性能高:SELECT  type ,STUFF((SELECT title+',' FROM wk_CarInfo 
 WHERE t_u.type=type order by title
FOR XML PATH('')),1,0,'') AS title
FROM  wk_CarInfo as t_u
GROUP BY type

 

转载于:https://www.cnblogs.com/flysem/p/10282205.html

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

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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值