----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-18 10:40:27
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[num] int,[name] varchar(1))
insert [huang]
select 1,2,'a' union all
select 2,3,'b' union all
select 3,3,'c' union all
select 4,4,'d' union all
select 5,4,'b'
--------------开始查询--------------------------select ROW_NUMBER()OVER(ORDER BY GETDATE())id,a.[num],
stuff((select ','+[name] from [huang] b
where b.[num]=a.[num]
for xml path('')),1,1,'') [name]
from [huang] a
group by a.[num]
----------------结果----------------------------
/* id num name
-------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 2 a
2 3 b,c
3 4 d,b
*/
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-18 10:40:27
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[num] int,[name] varchar(1))
insert [huang]
select 1,2,'a' union all
select 2,3,'b' union all
select 3,3,'c' union all
select 4,4,'d' union all
select 5,4,'b'
--------------开始查询--------------------------select ROW_NUMBER()OVER(ORDER BY GETDATE())id,a.[num],
stuff((select ','+[name] from [huang] b
where b.[num]=a.[num]
for xml path('')),1,1,'') [name]
from [huang] a
group by a.[num]
----------------结果----------------------------
/* id num name
-------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 2 a
2 3 b,c
3 4 d,b
*/
id=row_number()over(order by getdate()),*
from
(select num, [name]=stuff((select ','+[name] from tb where num=t.num for xml path('')), 1, 1, '')
from tb as t
group by id ) as t
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-18 10:40:27
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[num] int,[name] varchar(1))
insert [huang]
select 1,2,'a' union all
select 2,3,'b' union all
select 3,3,'c' union all
select 4,4,'d' union all
select 5,4,'b'
--------------开始查询--------------------------
go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+[name] from [huang] where [num]=@Col1
return @S
END
go
Select distinct IDENTITY(INT,1,1) AS id,[num],[name]=dbo.F_Str([num]) INTO #T from [huang]SELECT * FROM #t
----------------结果----------------------------
/* id num name
----------- ----------- ----------------------------------------------------------------------------------------------------
1 2 a
2 3 b,c
3 4 d,b*/