CREATE TABLE [dbo].[ta1] (
[t1] [char] (10) NULL
) ON [PRIMARY]
GO
插入數據(a/b)CREATE TABLE [dbo].[ta2] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[t1] [char] (10) NULL ,
[t3] [char] (10) NULL
) ON [PRIMARY]
GO插入數據(1/a/e,2/a/f,3/b/d)
CREATE TABLE [dbo].[ta3] (
[t3] [char] (10) NULL
) ON [PRIMARY]
GO
插入數據(e/d/f)select ta1.t1,
max(case ta3.t3 when 'e' then 'e' end ),
max(case ta3.t3 when 'd' then 'd' end),
max(case ta3.t3 when 'f' then 'f' end)
from ta1 join ta2 on ta1.t1=ta2.t1 join ta3 on ta2.t3=ta3.t3
group by ta1.t1
[t1] [char] (10) NULL
) ON [PRIMARY]
GO
插入數據(a/b)CREATE TABLE [dbo].[ta2] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[t1] [char] (10) NULL ,
[t3] [char] (10) NULL
) ON [PRIMARY]
GO插入數據(1/a/e,2/a/f,3/b/d)
CREATE TABLE [dbo].[ta3] (
[t3] [char] (10) NULL
) ON [PRIMARY]
GO
插入數據(e/d/f)select ta1.t1,
max(case ta3.t3 when 'e' then 'e' end ),
max(case ta3.t3 when 'd' then 'd' end),
max(case ta3.t3 when 'f' then 'f' end)
from ta1 join ta2 on ta1.t1=ta2.t1 join ta3 on ta2.t3=ta3.t3
group by ta1.t1
insert t2 select
1,'a','!' union select
2,'a','@' union select
3,'a','#' union select
4,'b','!' union select
5,'b','@'go
create function f_getString(@T1 char(1))
returns varchar(200)
begin
declare @s varchar(200)
select @s=''
select @s=@s+','+T3 from t2 where T1=@T1 order by id
select @s=substring(@s,2,len(@s))
return @s
endgoselect T1,dbo.f_getString(T1) as T2 from t2 group by T1--测试结果
T1 T2
---- ----------
a !,@,#
b !,@(2 row(s) affected)