27,579
社区成员
发帖
与我相关
我的任务
分享
if object_id('[A]') is not null drop table [A]
go
create table [A]([Id] nvarchar(20),[Count] int,[InvoiceNumber] int)
insert [A]
select 'A201406001',1,120001 union all
select 'A201406003',1,120003 union all
select 'A201406003',2,120004 union all
select 'A201406003',3,120005
go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 NVARCHAR(100))
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+CAST([InvoiceNumber] AS NVARCHAR(100)) from [A] where [Id]=@Col1
return @S
end
go
Select distinct [Id],[InvoiceNumber]=dbo.F_Str([Id]) from [A]
/*
Id InvoiceNumber
-------------------- ----------------------------------------------------------------------------------------------------
A201406001 120001
A201406003 120003,120004,120005
*/
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-25 13:49:04
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([Id] nvarchar(20),[Count] int,[InvoiceNumber] int)
insert [A]
select 'A201406001',1,120001 union all
select 'A201406003',1,120003 union all
select 'A201406003',2,120004 union all
select 'A201406003',3,120005
--------------生成数据--------------------------
select a.[Id],
stuff((select ','+CAST([InvoiceNumber] AS VARCHAR(30)) from [A] b
where b.[Id]=a.[Id]
for xml path('')),1,1,'') [InvoiceNumber]
from [A] a
group by a.[Id]
----------------结果----------------------------
/*
Id InvoiceNumber
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A201406001 120001
A201406003 120003,120004,120005
*/