DECLARE tb CURSOR LOCAL FOR SELECT col1,col2 FROM tb ORDER BY col1,col2 DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100) OPEN tb FETCH tb INTO @col1,@col2 SELECT @col1_old=@col1,@s='' WHILE @@FETCH_STATUS=0 BEGIN IF @col1=@col1_old SELECT @s=@s+'&'+CAST(@col2 as varchar) ELSE BEGIN INSERT @t VALUES(@col1_old,STUFF(@s,1,1,'')) SELECT @s='&'+CAST(@col2 as varchar),@col1_old=@col1 END FETCH tb INTO @col1,@col2 END INSERT @t VALUES(@col1_old,STUFF(@s,1,1,'')) CLOSE tb DEALLOCATE tb --显示结果并删除测试数据 SELECT * FROM @t DROP TABLE tb
a b 5001 a 5001 b 5001 d 5002 e 5002 a create function f_str(@a int) returns varchar(100) as begin declare @s varchar(1000) select @s = isnull(@s+'&','')+b from ta where a = @a return @s end go select a,dbo.f_str(a) from ta group by a
if object_id('test') is not null drop table test create table tb (col1 int, col2 varchar(20))insert into tb select 5001,'a' union select 5001,'b' union select 5001,'d' union select 5002,'e' union select 5002,'a'DECLARE @t TABLE(col1 varchar(10),col2 varchar(100)) DECLARE tb CURSOR LOCAL FOR SELECT col1,col2 FROM tb ORDER BY col1,col2 DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 varchar(10),@s varchar(100) OPEN tb FETCH tb INTO @col1,@col2 SELECT @col1_old=@col1,@s='' WHILE @@FETCH_STATUS=0 BEGIN IF @col1=@col1_old SELECT @s=@s+'&'+CAST(@col2 as varchar) ELSE BEGIN INSERT @t VALUES(@col1_old,STUFF(@s,1,1,'')) SELECT @s='&'+CAST(@col2 as varchar),@col1_old=@col1 END FETCH tb INTO @col1,@col2 END INSERT @t VALUES(@col1_old,STUFF(@s,1,1,'')) CLOSE tb DEALLOCATE tb --显示结果并删除测试数据 SELECT * FROM @t DROP TABLE tb-------------- 5001 a&b&d 5002 a&e
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2010-01-21 15:15:46 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[value] varchar(1)) insert [tb] select 5001,'a' union all select 5001,'b' union all select 5001,'d' union all select 5002,'e' union all select 5002,'a' --------------开始查询-------------------------- select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '') from tb group by id ----------------结果---------------------------- /* id values ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 5001 a,b,d 5002 e,a(2 行受影响) */
create table tablea ( code varchar(5), col varchar(2) ) goinsert into tablea select '5001', 'a' union all select '5001', 'b' union all select '5001', 'd' union all select '5002', 'e' union all select '5002', 'a' go ;with t as (select code,(select col+',' from tablea where code=a.code for xml path('')) as newcol from tablea a group by code) select code,stuff(newcol,len(newcol),1,'')as col from t
DECLARE tb CURSOR LOCAL
FOR
SELECT col1,col2 FROM tb ORDER BY col1,col2
DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col1,@s=''
WHILE @@FETCH_STATUS=0
BEGIN
IF @col1=@col1_old
SELECT @s=@s+'&'+CAST(@col2 as varchar)
ELSE
BEGIN
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
SELECT @s='&'+CAST(@col2 as varchar),@col1_old=@col1
END
FETCH tb INTO @col1,@col2
END
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
CLOSE tb
DEALLOCATE tb
--显示结果并删除测试数据
SELECT * FROM @t
DROP TABLE tb
5001 a
5001 b
5001 d
5002 e
5002 a
create function f_str(@a int)
returns varchar(100)
as
begin
declare @s varchar(1000)
select @s = isnull(@s+'&','')+b from ta where a = @a
return @s
end
go
select a,dbo.f_str(a)
from ta
group by a
if object_id('test') is not null drop table test
create table tb
(col1 int,
col2 varchar(20))insert into tb
select 5001,'a' union
select 5001,'b' union
select 5001,'d' union
select 5002,'e' union
select 5002,'a'DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))
DECLARE tb CURSOR LOCAL
FOR
SELECT col1,col2 FROM tb ORDER BY col1,col2
DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 varchar(10),@s varchar(100)
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col1,@s=''
WHILE @@FETCH_STATUS=0
BEGIN
IF @col1=@col1_old
SELECT @s=@s+'&'+CAST(@col2 as varchar)
ELSE
BEGIN
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
SELECT @s='&'+CAST(@col2 as varchar),@col1_old=@col1
END
FETCH tb INTO @col1,@col2
END
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
CLOSE tb
DEALLOCATE tb
--显示结果并删除测试数据
SELECT * FROM @t
DROP TABLE tb--------------
5001 a&b&d
5002 a&e
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2010-01-21 15:15:46
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[value] varchar(1))
insert [tb]
select 5001,'a' union all
select 5001,'b' union all
select 5001,'d' union all
select 5002,'e' union all
select 5002,'a'
--------------开始查询--------------------------
select
id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from
tb
group by
id
----------------结果----------------------------
/* id values
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5001 a,b,d
5002 e,a(2 行受影响)
*/
create table tablea
(
code varchar(5),
col varchar(2)
)
goinsert into tablea
select '5001', 'a' union all
select '5001', 'b' union all
select '5001', 'd' union all
select '5002', 'e' union all
select '5002', 'a'
go
;with t as
(select code,(select col+',' from tablea where code=a.code for xml path('')) as newcol from tablea a group by code)
select code,stuff(newcol,len(newcol),1,'')as col from t