求一条SQL语句??
有一张表table,如下
col1 col2
101 aaa
101 bbb
101 ccc
102 ddd
102 eee
我想用SQL语句将它变成如下的结构,
cola colb
101 aaabbbccc
102 dddeee
请问用SQL语句如何实现???
[解决办法]
create table T(col1 int, col2 varchar(10))
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
end
select col1, col2=dbo.fun(col1) from T group by col1
[解决办法]
col1 col2
----------- --------------------------------------------------------------------------------------------
101 aaabbbccc
102 dddeee
(2 row(s) affected)
[解决办法]
create table tab (col1 varchar(10),col2 varchar(10))
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 tb
drop table tb
drop function f_hb
result:
cola colb
----------- -----------
101 aaabbbccc
102 dddeee
（所影响的行数为 2 行）
------解决方案--------------------
上面的代码已经很好了,这里我就不多说了,我要添加的一句就是,列名也是要修改的,上面的几位仁兄可能忘记了,再加上下面这句话吧:
exec sp_rename 'T.col1 ', 'cola ', 'column '
exec sp_rename 'T.col2 ', 'colb ', 'column '
大家都是学生,望以后共同进步哦,取得成就!!!
