--测试数据
create table csdn(id int,txt varchar(10))
insert csdn
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 2,'aa' union all
select 2,'bb' union all
select 2,'cc' union all
select 3,'aaa' union all
select 3,'bbb'
select * from csdn
gocreate function Gettxt(@id int)
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s +',' +txt from csdn where id=@id
--return @s
return stuff(@s,1,1,'')
end
goselect id,dbo.Gettxt(id) txt from csdn group by id
godrop function Gettxt
drop table csdn
go
create table csdn(id int,txt varchar(10))
insert csdn
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 2,'aa' union all
select 2,'bb' union all
select 2,'cc' union all
select 3,'aaa' union all
select 3,'bbb'
select * from csdn
gocreate function Gettxt(@id int)
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s +',' +txt from csdn where id=@id
--return @s
return stuff(@s,1,1,'')
end
goselect id,dbo.Gettxt(id) txt from csdn group by id
godrop function Gettxt
drop table csdn
go
a1 a2 a3
a 123 456
if object_id('test') is not null drop table test
select 1 as Id, '语文' as col1, 88 as col2
into test
union select 1, '数学', 89
union select 2, '语文', 90
union select 2, '数学', 91
----------------------------------------------------------------------
declare @s varchar(1000)
set @s = 'select Id'
select @s = @s + ', sum(case when col1 = ''' + col1 + ''' then col2 end) as [' + col1 + ']'
from (select distinct col1 from test) a
set @s = @s + ' from test group by Id'
exec(@s)
/*
Id 数学 语文
----------- ----------- -----------
1 89 88
2 91 90
*/
----------------------------------------------------------------------
drop table test