----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-11-21 17:20:44
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([客户ID] int,[来源] varchar(4),[采集时间] datetime)
insert [huang]
select 1111,'赶集','2014-05-05' union all
select 1112,'搜狐','2014-05-03' union all
select 1112,'新浪','2014-05-03' union all
select 1111,'新浪','2014-05-05' union all
select 1111,'搜狐','2014-05-05' union all
select 1113,'新浪','2014-05-06'
--------------开始查询--------------------------select a.[客户ID],
stuff((select ','+[来源] from [huang] b
where b.[客户ID]=a.[客户ID]
for xml path('')),1,1,'') '来源'
from [huang] a
group by a.[客户ID]
----------------结果----------------------------
/*
客户ID 来源
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1111 赶集,新浪,搜狐
1112 搜狐,新浪
1113 新浪*/
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-11-21 17:20:44
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([客户ID] int,[来源] varchar(4),[采集时间] datetime)
insert [huang]
select 1111,'赶集','2014-05-05' union all
select 1112,'搜狐','2014-05-03' union all
select 1112,'新浪','2014-05-03' union all
select 1111,'新浪','2014-05-05' union all
select 1111,'搜狐','2014-05-05' union all
select 1113,'新浪','2014-05-06'
--------------开始查询--------------------------select a.[客户ID],
stuff((select ','+[来源] from [huang] b
where b.[客户ID]=a.[客户ID]
for xml path('')),1,1,'') '来源'
from [huang] a
group by a.[客户ID]
----------------结果----------------------------
/*
客户ID 来源
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1111 赶集,新浪,搜狐
1112 搜狐,新浪
1113 新浪*/
drop table test
go
create table test([客户ID] int,[来源] varchar(4),[采集时间] datetime)
insert test select 1111,'赶集','2014-05-05'union all
select 1112,'搜狐','2014-05-03' union all
select 1112,'新浪','2014-05-03' union all
select 1111,'新浪','2014-05-05' union all
select 1111,'搜狐','2014-05-05' union all
select 1113,'新浪','2014-05-06'go
create function fn_sumstring (@id int)
returns Nvarchar(400)
as begin
declare @reval Nvarchar(400)
select @reval=isnull(@reval+',','')+[来源]
from test
where [客户ID]=@id
return @reval
end
go
select [客户ID],dbo.fn_sumstring([客户ID]) as [来源]
from test
group by [客户ID]
有关stuff函数,看msdnhttp://msdn.microsoft.com/zh-cn/library/ms188043(v=sql.105).aspx
;WITH cte AS(
select 1111 AS id,'赶集' as name,'2014-05-05'as [time] union all
select 1112,'搜狐','2014-05-03' union all
select 1112,'新浪','2014-05-03' union all
select 1111,'新浪','2014-05-05' union all
select 1111,'搜狐','2014-05-05' union all
select 1113,'新浪','2014-05-06'
)
SELECT
CT.ID,
STUFF((SELECT ','+NAME FROM cte C WHERE C.ID=CT.ID FOR XML PATH('')),1,1,'')AS NAME FROM CTE CT
GROUP BY CT.ID