你的位置:首页 > 数据库

[数据库]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

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.townconcatgo

二、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

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

 

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

 

四、分析:
以上3种方法各有优劣,个人喜欢for

核心的代码是:

SELECT town+','FROM t2 FOR '')

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

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

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

 

那我们如何根据关键字段来分组呢,我们可以把(select ..FOR

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