有一张表table,如下
col1 col2
101 aaa
101 bbb
101 ccc
102 ddd
102 eee
我想用SQL语句将它变成如下的结构,
cola colb
101 aaabbbccc
102 dddeee
请问用SQL语句如何实现???
col1 col2
101 aaa
101 bbb
101 ccc
102 ddd
102 eee
我想用SQL语句将它变成如下的结构,
cola colb
101 aaabbbccc
102 dddeee
请问用SQL语句如何实现???
insert T select 101, 'aaa'
insert T select 101, 'bbb'
insert T select 101, 'ccc'
insert T select 102, 'ddd'
insert T select 102, 'eee'create function fun(@col1 int)
returns varchar(200)
as
begin
declare @re varchar(200)
set @re=''
select @re=@re+col2 from T where col1=@col1 return @re
endselect col1, col2=dbo.fun(col1) from T group by col1
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
101 aaabbbccc
102 dddeee(2 row(s) affected)
insert tab
select '101', 'aa'
union all
select '101', 'bb'
union all
select '101', 'cc'
union all
select '102', 'dd'
union all
select '102', 'ee'
create function rowtocolumn(@id varchar(10))
returns varchar(1000)
as
begin
declare @str varchar(1000) set @str = ''
select @str = @str + col2 from tab where col1 = @id return @str
end select col1,dbo.rowtocolumn(col1) from tab group by col1
create table tb
(
col1 int,
col2 char(10)
)
insert into tb(col1,col2) values(101,'aaa')
insert into tb(col1,col2) values(101,'bbb')
insert into tb(col1,col2) values(101,'ccc')
insert into tb(col1,col2) values(102,'ddd')
insert into tb(col1,col2) values(102,'eee')go
create function f_hb(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + '' + rtrim(col2) from tb where col1 = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
select distinct col1 as cola,dbo.f_hb(col1) as colb from tbdrop table tb
drop function f_hb
result:
cola colb
----------- -----------
101 aaabbbccc
102 dddeee(所影响的行数为 2 行)
exec sp_rename 'T.col2','colb','column'大家都是学生,望以后共同进步哦,取得成就!!!
不过还是谢谢大家