表 A
Id(单号),Count(序号),InvoiceNumber(发票号码)
A201406001, 1, 120001
A201406003, 1, 120003
A201406003, 2, 120004
A201406003, 3, 120005
求显示成
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
*/