--有表t,其数据如下:
a b
1 1
1 2
1 3
2 1
2 2
3 1
--如何转换成如下结果:
a b
1 1,2,3
2 1,2
3 1 create table tb
(
a int,
b int
)
insert into tb(a,b) values(1,1)
insert into tb(a,b) values(1,2)
insert into tb(a,b) values(1,3)
insert into tb(a,b) values(2,1)
insert into tb(a,b) values(2,2)
insert into tb(a,b) values(3,1)
go--创建一个合并的函数
create function f_hb(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(b as varchar) from tb where a = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct a ,dbo.f_hb(a) as b from tbdrop table tb--结果
a b
----------- ------
1 1,2,3
2 1,2
3 1(所影响的行数为 3 行)
a b
1 1
1 2
1 3
2 1
2 2
3 1
--如何转换成如下结果:
a b
1 1,2,3
2 1,2
3 1 create table tb
(
a int,
b int
)
insert into tb(a,b) values(1,1)
insert into tb(a,b) values(1,2)
insert into tb(a,b) values(1,3)
insert into tb(a,b) values(2,1)
insert into tb(a,b) values(2,2)
insert into tb(a,b) values(3,1)
go--创建一个合并的函数
create function f_hb(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(b as varchar) from tb where a = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct a ,dbo.f_hb(a) as b from tbdrop table tb--结果
a b
----------- ------
1 1,2,3
2 1,2
3 1(所影响的行数为 3 行)
create function f_str(@plan_id int,@plan_executive_date varchar(10),@executive_user varchar(10))
returns varchar(1000)
as
begin
delcare @str varchar(1000)
set @str=''
select @str=@str+','+position from 源数据表 where plan_id=@plan_id and plan_executive_date=@plan_executive_date and executive_user=@executive_user
set @str=stuff(@str,1,1,'')
return @str
endgoselect plan_id,
convert(varchar(10),plan_executive_date,120) as plan_executive_date,
dbo.f_str(plan_id,convert(varchar(10),plan_executive_date,120),executive_user) as position,
executive_user
from 源数据表
group by plan_id,convert(varchar(10),plan_executive_date,120),executive_user
create function f_hb(@PLAN_ID varchar , @PLAN_EXECUTIVE_TIME varchar ,@EXECUTIVE_USER varchar)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(POSITION as varchar)
from tb
where PLAN_ID = @PLAN_ID and
convert(varchar(10),PLAN_EXECUTIVE_TIME,120) = @PLAN_EXECUTIVE_TIME and
EXECUTIVE_USER = @EXECUTIVE_USER
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct PLAN_ID , convert(varchar(10),PLAN_EXECUTIVE_TIME ,120) as PLAN_EXECUTIVE_TIME , EXECUTIVE_USER , dbo.f_hb(PLAN_ID , PLAN_EXECUTIVE_TIME , EXECUTIVE_USER) as b from tb
IT'S VERY USERFUL ME.
It's the last function I use. It's as following
/////////////////
CREATE function fn_Signature(@plan_id int,@user_name varchar(250))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + EQUIPMENT_NAME FROM IIMS_RECORD_USER_SIGNATURE
WHERE PLAN_ID = @plan_id AND [USER_NAME] = @user_name
SET @str = right(@str,len(@str)-1)
return (@str)
end
///////////////////
Thanks guys!!!!!!!!!