create table TBA( col1 varchar(10), col2 varchar(5) )
go
insert into tba
select 'A' ,'1' union all
select 'A' ,'2' union all
select 'A' ,'3' union all
select 'B' ,'1' union all
select 'B' ,'6' union all
select 'C' ,'2' union all
select 'C' ,'3'
希望得到这样的结果
col1 col2
A 1,2,3
B 1,6
C 2,3
go
insert into tba
select 'A' ,'1' union all
select 'A' ,'2' union all
select 'A' ,'3' union all
select 'B' ,'1' union all
select 'B' ,'6' union all
select 'C' ,'2' union all
select 'C' ,'3'
希望得到这样的结果
col1 col2
A 1,2,3
B 1,6
C 2,3
drop table tba
go
create table TBA( col1 varchar(10), col2 varchar(5) )
insert into tba
select 'A' ,'1' union all
select 'A' ,'2' union all
select 'A' ,'3' union all
select 'B' ,'1' union all
select 'B' ,'6' union all
select 'C' ,'2' union all
select 'C' ,'3'GO
if object_id('fntry') is not null
drop function fntry
GO
----创建字符串连接函数
create function fntry(@id varchar(10))
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + ',' + col2 from TBA where col1 = @id
return stuff(@str,1,1,'')
end
GO
----调用函数
select col1,dbo.fntry(col1) from TBA group by col1
--也可以用临时表
DECLARE @col1 VARCHAR(10) ,@col2 VARCHAR(5)
SELECT col1,col2 INTO #T FROM TBA ORDER BY col1,col2
UPDATE #T SET
@COL2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END,
@col1=col1,
col2=@col2
SELECT * FROM #T
SELECT col1,col2=MAX(col2)FROM #T GROUP BY col1
DROP TABLE #T
= CSDN助手 全面支持CSDN论坛 =
= 监视、收藏、历史、签名走马灯 =
==================================