create TABLE TT (id varchar(32),type varchar(32),billMoney decimal(12,2)) INSERT INTO TT SELECT '10001','支票',2000 UNION ALL SELECT '10001','预留款',1000 UNION ALL SELECT '10001','现金',800 UNION ALL SELECT '10002','挂账',1000 UNION ALL SELECT '10002','现金',800create function dbo.FC_Str(@id int) returns varchar(1000) as begin declare @str varchar(1000) set @str='' select @str=@str+','+type+':'+cast(billMoney as varchar) from TT where id=@id return stuff(@str,1,1,'') endselect id,sum(billMoney) payedMoney,dbo.FC_Str(id) from TT group by id本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/ws_hgo/archive/2009/03/17/3999390.aspx
create table tb (列1 int, 列2 varchar(2)) insert into tb select 1, 'a' union all select 1, 'b' union all select 1, 'c'select 列1,stuff((select ','+列2 from tb where 列1=a.列1 for xml path('')),1,1,'') 列2 from tb a group by a.列1列1 列2 1 a,b,c
--sql 2000if object_id('tb') is not null drop table tb go create table tb(列1 int,列2 varchar(10)) insert tb select 1 ,'a' union all select 1 ,'b' union all select 1 ,'c' go if object_id('f_str') is not null drop function f_str go create function dbo.f_str(@id int) returns varchar(100) as begin declare @str varchar(1000) set @str = '' select @str = @str + ',' + 列2 from tb where 列1 = @id set @str = right(@str , len(@str) - 1) return @str end goselect 列1,dbo.f_str(列1) 列2 from tb group by 列1/*(所影响的行数为 3 行)列1 列2 ----------- ---------------------------------------------------------------------------------------------------- 1 a,b,c(所影响的行数为 1 行)*/
select 列1,列2=stuff(select ','+列2 from table1 T where T.列1=列1 for xml path(''),1,1,'') From Table1 group by 列1
create table tb(id varchar(10),col varchar(10)) insert into tb select '1','a' union all select '1','b' union all select '1','c' union all select '2','f' union all select '2','d' union all select '2','k' union all select '3','q'select * from tb create function dbo.fn_name(@id as varchar(20)) returns varchar(500) as begin declare @col varchar(500) set @col='' select @col=@col+col+',' from tb where id=@id if isnull(@col,'')<>'' set @col=left(@col,len(@col)-1) return (@col) end/* drop function dbo.fn_name drop table tb */select id,dbo.fn_name(id) as col2 from tb group by id结果 id col2 ------------ 1 a,b,c 2 f,d,k 3 q
if object_id('f_str')is not null drop function f_str go create function f_str(@id int) returns varchar(400) as begin declare @str varchar(400) select @str=isnull(@str+',','')+列2 from 表名 where 列1=@id return @str end go select 列1 ,列2=dbo.f_str(列1 ) from 表名 group by 列1
试一下这个:select [列1],[列2]=stuff((select ','+[列2] from tb T where T.[列1]=[列1] for xml path('')),1,1,'') From tb group by [列1]
create table TT ( 列1 int, 列2 varchar(20) ) insert into TT select 1,'a' insert into TT select 1,'b' insert into TT select 1,'c' drop function dbo.GetStr create function dbo.GetStr(@Col1 int) returns varchar(1000) as begin declare @sql varchar(1000) set @sql='' select @sql=@sql+','+列2 from TT where 列1=@Col1 return stuff(@sql,1,1,'') endselect distinct 列1,dbo.GetStr(列1) 列2 from TT列1 列2 ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 a,b,c(1 行受影响)
INSERT INTO TT
SELECT '10001','支票',2000
UNION ALL
SELECT '10001','预留款',1000
UNION ALL
SELECT '10001','现金',800
UNION ALL
SELECT '10002','挂账',1000
UNION ALL
SELECT '10002','现金',800create function dbo.FC_Str(@id int)
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=@str+','+type+':'+cast(billMoney as varchar) from TT where id=@id
return stuff(@str,1,1,'')
endselect id,sum(billMoney) payedMoney,dbo.FC_Str(id) from TT group by id本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/ws_hgo/archive/2009/03/17/3999390.aspx
insert into tb
select 1, 'a' union all
select 1, 'b' union all
select 1, 'c'select 列1,stuff((select ','+列2
from tb
where 列1=a.列1 for xml path('')),1,1,'') 列2
from tb a
group by a.列1列1 列2
1 a,b,c
--sql 2000if object_id('tb') is not null drop table tb
go
create table tb(列1 int,列2 varchar(10))
insert tb
select 1 ,'a' union all
select 1 ,'b' union all
select 1 ,'c'
go
if object_id('f_str') is not null drop function f_str
go
create function dbo.f_str(@id int) returns varchar(100)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + ',' + 列2 from tb where 列1 = @id
set @str = right(@str , len(@str) - 1)
return @str
end
goselect 列1,dbo.f_str(列1) 列2 from tb group by 列1/*(所影响的行数为 3 行)列1 列2
----------- ----------------------------------------------------------------------------------------------------
1 a,b,c(所影响的行数为 1 行)*/
insert into tb
select '1','a'
union all select '1','b'
union all select '1','c'
union all select '2','f'
union all select '2','d'
union all select '2','k'
union all select '3','q'select * from tb
create function dbo.fn_name(@id as varchar(20))
returns varchar(500)
as
begin
declare @col varchar(500)
set @col=''
select @col=@col+col+','
from tb where id=@id
if isnull(@col,'')<>''
set @col=left(@col,len(@col)-1)
return (@col)
end/*
drop function dbo.fn_name
drop table tb
*/select id,dbo.fn_name(id) as col2
from tb
group by id结果
id col2
------------
1 a,b,c
2 f,d,k
3 q
go
create function f_str(@id int)
returns varchar(400)
as
begin
declare @str varchar(400)
select @str=isnull(@str+',','')+列2
from 表名 where 列1=@id return @str
end
go
select 列1 ,列2=dbo.f_str(列1 ) from 表名
group by 列1
(
列1 int,
列2 varchar(20)
)
insert into TT select 1,'a'
insert into TT select 1,'b'
insert into TT select 1,'c'
drop function dbo.GetStr
create function dbo.GetStr(@Col1 int)
returns varchar(1000)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+列2 from TT where 列1=@Col1
return stuff(@sql,1,1,'')
endselect distinct 列1,dbo.GetStr(列1) 列2 from TT列1 列2
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 a,b,c(1 行受影响)