有表A,
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1
或者是从表B变成A(不要用游标)
以前有相似的列子,现在找不到了,帮帮忙!
--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
go--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1
或者是从表B变成A(不要用游标)
以前有相似的列子,现在找不到了,帮帮忙!
--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
go--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+'、'+rtrim(CODE_NAME) from TABLE2 where T2_AB_ORDER_ID=@id
set @str=right(@str,len(@str)-1)
return(@str)
go--调用“
select AB_ORDER_ID,dbo.getstr(AB_ORDER_ID) from TABLE1 group by AB_ORDER_ID
insert d values('001','y')
insert d values('002','y')
insert d values('002','x')
insert d values('003','z')
insert d values('003','p')
gocreate function test1(@a varchar(20))
returns varchar(200)
as
begin
declare @b varchar(200)
set @b=''
select @b=@b+rtrim(ltrim(F2)) from d where f1=@a
return @b
endselect distinct f1,dbo.test1(f1) from d
select distinct f1,dbo.test1(f1) from d