---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2014-01-10 17:09:25 -- Version: -- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) -- Dec 28 2012 20:23:12 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) -- ---------------------------------------------------------------- --> 测试数据:[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([PROID] int,[PROSN] int) insert [huang] select 10,10001 union all select 10,10002 union all select 10,10003 --------------开始查询--------------------------select a.[PROID], stuff((select ','+CAST([PROSN] AS VARCHAR) from [huang] b where b.[PROID]=a.[PROID] for xml path('')),1,1,'') 'PROSN' from [huang] a group by a.[PROID] ----------------结果---------------------------- /* PROID PROSN ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 10 10001,10002,10003 */
CREATE TABLE feng (proid int,prosn varchar(50)) GO INSERT feng VALUES (10,1001),(10,1002),(10,1003) GOSELECT LEFT(REPLACE(REPLACE(CAST((SELECT prosn FROM feng FOR XML AUTO)AS VARCHAR(4000)),'<feng prosn="',''),'"/>',',') ,LEN(REPLACE(REPLACE(CAST((SELECT prosn FROM feng FOR XML AUTO)AS VARCHAR(4000)),'<feng prosn="',''),'"/>',','))-1)
SQL SERVER 2000在XML那里报错
---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2014-01-10 17:09:25 -- Version: -- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) -- Dec 28 2012 20:23:12 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) -- ---------------------------------------------------------------- --> 测试数据:[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([PROID] int,[PROSN] int) insert [huang] select 10,10001 union all select 10,10002 union all select 10,10003 --------------开始查询--------------------------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+',','')+CAST([PROSN] AS VARCHAR) from [huang] where [PROID]=@Col1 return @S end go Select distinct [PROID],PROSN=dbo.F_Str([PROID]) from [huang] ----------------结果---------------------------- /* PROID PROSN ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 10 10001,10002,10003 */
我就知道没那么简单,2005开始才有XML,用8楼的
前面的insert修改一下,SQL2000能搞定 CREATE TABLE feng (proid int,prosn varchar(50)) GO INSERT feng VALUES (10,1001) INSERT feng VALUES (10,1002) INSERT feng VALUES (10,1003) GOSELECT LEFT(REPLACE(REPLACE(CAST((SELECT prosn FROM feng FOR XML AUTO)AS VARCHAR(4000)),'<feng prosn="',''),'"/>',',') ,LEN(REPLACE(REPLACE(CAST((SELECT prosn FROM feng FOR XML AUTO)AS VARCHAR(4000)),'<feng prosn="',''),'"/>',','))-1)
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-10 17:09:25
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([PROID] int,[PROSN] int)
insert [huang]
select 10,10001 union all
select 10,10002 union all
select 10,10003
--------------开始查询--------------------------select a.[PROID],
stuff((select ','+CAST([PROSN] AS VARCHAR) from [huang] b
where b.[PROID]=a.[PROID]
for xml path('')),1,1,'') 'PROSN'
from [huang] a
group by a.[PROID]
----------------结果----------------------------
/*
PROID PROSN
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10 10001,10002,10003
*/
GO
INSERT feng VALUES (10,1001),(10,1002),(10,1003)
GOSELECT
LEFT(REPLACE(REPLACE(CAST((SELECT prosn FROM feng FOR XML AUTO)AS VARCHAR(4000)),'<feng prosn="',''),'"/>',',')
,LEN(REPLACE(REPLACE(CAST((SELECT prosn FROM feng FOR XML AUTO)AS VARCHAR(4000)),'<feng prosn="',''),'"/>',','))-1)
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-10 17:09:25
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([PROID] int,[PROSN] int)
insert [huang]
select 10,10001 union all
select 10,10002 union all
select 10,10003
--------------开始查询--------------------------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+',','')+CAST([PROSN] AS VARCHAR) from [huang] where [PROID]=@Col1
return @S
end
go
Select distinct [PROID],PROSN=dbo.F_Str([PROID]) from [huang]
----------------结果----------------------------
/*
PROID PROSN
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10 10001,10002,10003
*/
CREATE TABLE feng (proid int,prosn varchar(50))
GO
INSERT feng VALUES (10,1001)
INSERT feng VALUES (10,1002)
INSERT feng VALUES (10,1003)
GOSELECT
LEFT(REPLACE(REPLACE(CAST((SELECT prosn FROM feng FOR XML AUTO)AS VARCHAR(4000)),'<feng prosn="',''),'"/>',',')
,LEN(REPLACE(REPLACE(CAST((SELECT prosn FROM feng FOR XML AUTO)AS VARCHAR(4000)),'<feng prosn="',''),'"/>',','))-1)