--可以用函数来解决
create function test(@id int)
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+cast(score as varchar)+':'+cast(stu as varchar)+':'+date+';' from b where aid=@id
set @sql=left(@sql,len(@sql)-1)
set @sql='['+@sql+']'
return(@sql)
end--执行
select id,name,sex,date,dbo.test(id) as asdf from a
create function test(@id int)
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+cast(score as varchar)+':'+cast(stu as varchar)+':'+date+';' from b where aid=@id
set @sql=left(@sql,len(@sql)-1)
set @sql='['+@sql+']'
return(@sql)
end--执行
select id,name,sex,date,dbo.test(id) as asdf from a
set @Sql='',
select @Sql=@Sql+';'+convert(varhcar(10),score)+':'+convert(varhcar(10),stu)+':'+convert(varchar(10),www)) from B where aid=1
set @Sql=right(@Sql,len(@Sql)-1)
select id,name,sex,date,@Sql as ss from A where id=1
create function test(@id int)
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+cast(score as varchar)+':'+cast(stu as varchar)+':'+www+';' from b where aid=@id
set @sql=left(@sql,len(@sql)-1)
set @sql='['+@sql+']'
return(@sql)
end--执行
select id,name,sex,date,dbo.test(id) as asdf from a
create table bb(id int,aid int,score int,stu int,www varchar(100))insert into aa select 1,'小王','男','2003-10-4'
insert into bb select 1,1,54,999,'无'
insert into bb select 2,1,87,222,'有'create function fun(@aid int)
returns varchar(4000) as
begin
declare @str varchar(8000)
select @str=''
select @str=@str+convert(varchar(4000),score)+':'+convert(varchar(4000),stu)+':'+www+':'
from bb where aid=@aid
select @str=left(@str,len(@str)-1)
return @str
endselect a.*,b.jia
from aa a join
(select max(id) id,aid,jia
from (
select *,dbo.fun(aid) jia from bb) temb
group by aid,jia) b
on a.id=b.aid
Line 1: Incorrect syntax near 'function'.
Server: Msg 137, Level 15, State 1, Line 7
Must declare the variable '@id'.
Server: Msg 178, Level 15, State 1, Line 8
A RETURN statement with a return status can only be used in a stored procedure.
检查你的语句
第一行是否有(@id int)
as
declare @sql varchar(8000)
declare @Count int
declare @myTd int
set @myId=1
set @sql=''
select
IDENTITY(int,1,1) as Id,
@sql=@sql+cast(score as varchar)+':'+cast(stu as varchar)+':'+www+';' as mysql to #myTable from b inner join a on a.id=b.aidselect @count=Max(Id) from #myTempTable
select @mySql varchar(60)
WHILE @count <= @myID
BEGIN
select @mySql=mySql from #myTable where ID=@myID
set @mySql=left(@sql,len(@sql)-1)
set @mySql='['+@sql+']'
insert into a ("新列的列名")
values(@mySql)
@myId=@myID +1
Go
create procedure test
as
declare @sql varchar(8000)
declare @Count int
declare @myTd int
set @myId=1
set @sql=''
select
IDENTITY(int,1,1) as Id,
@sql=@sql+cast(score as varchar)+':'+cast(stu as varchar)+':'+www+';' as mysql to #myTable from b inner join a on a.id=b.aidselect @count=Max(Id) from #myTempTable
alter table a
add 新列的列名 varchar(60) NULL
select @mySql varchar(60)
WHILE @count <= @myID
BEGIN
select @mySql=mySql from #myTable where ID=@myID
set @mySql=left(@sql,len(@sql)-1)
set @mySql='['+@sql+']'
insert into a ("新列的列名")
values(@mySql)
@myId=@myID +1
end
Go
更正::
/**************************已经调试通过************************/
create procedure test
as
declare @sql varchar(8000)
declare @Count int
set @sql=''
select
IDENTITY(int,1,1) as Id,
cast(score as varchar)+':'+cast(stu as varchar)+':'+www+';' as [mysql] into #myTable from b inner join a on a.id=b.aidselect @count=Max(Id) from #myTempTable
alter table a -------添加新的列
add 新列的列名 varchar(60) NULL
declare @mySql varchar(60)
declare @myId int
set @myId=1
WHILE @count <=@myId
BEGIN
select @mySql=mySql from #myTable where ID=@myId
set @mySql=left(@mySql,len(@sql)-1)
set @mySql='['+@mySql+']'
INSERT INTO a (添加新的列)
VALUES (@mySql)set @myId=@myID +1
end
drop table #myTable --删除临时表。
Go
select @count=Max(Id) from #myTable
set www=(select aid from b join a on A.id=B.aid)
where a.id in (select aid from b join a on a.id=b.aid)