行转列的问题?

[wjj3000]

用例表:
---------------
id list
---------------
1 a
1 b
1 c
1 d
2 a
2 d
2 e
3 d
3 i
3 k
4 c
4 s
......
把这个表转换成下表,不能游标。
---------------
id list
---------------
1 a,b,c,d
2 a,e,d
3 d,i,k
4 c,s
......

[zlp321002]

--写个函数就可以了..
Create Function F_GetStr(@id)
returns varchar(200)
as
begin
declare @s varchar(200)
set @s=''
select @s=','+list+'' from
(select list from 用例表 where id=@id) A
set @s=stuff(@s,1,1,'')
return @s
end

--查询
select ID,F_GetStr(ID) from 用例表
group by ID

[zlp321002]

--注意,函数参考要指明,我忘记指明参数类型和大小了!

Create Function F_GetStr(@id varchar(20))
returns varchar(200)
as
begin
declare @s varchar(200)
set @s=''
select @s=','+list+'' from
(select list from 用例表 where id=@id) A
set @s=stuff(@s,1,1,'')
return @s
end

[wgsasd311]

create function f_1(@id int)
returns varchar(4000)
as
begin
declare @s varchar(4000)
set @s=''
select @s=@s+','+list from tb where id=@id
return left(@s,len(@s)-1)
end
go
select id,list=dbo.f_1(id) from tb group by id

[zlp321002]

--测试环境
create table 用例表 (id int,list varchar(10))
insert into 用例表 select 1,'a'
union all select 1,'b'
union all select 1,'c'
union all select 1,'d'
union all select 2,'a'
union all select 2,'d'
union all select 2,'e'
union all select 3,'d'
union all select 3,'i'
union all select 3,'k'
union all select 4,'c'
union all select 4,'s'

--建函数
Create Function F_GetStr(@id varchar(5))
returns varchar(200)
as
begin
declare @s varchar(200)
set @s=''
select @s=@s+','+list+'' from
(select list from 用例表 where id=@id) A
set @s=stuff(@s,1,1,'')
return @s
end

--查询
select ID,List=dbo.F_GetStr(ID) from 用例表
group by ID

--结果
ID List
------- -------
1 a,b,c,d
2 a,d,e
3 d,i,k
4 c,s

--删除测试环境
Drop table 用例表
Drop Function F_GetStr

[gimy007]

一楼正解

[wushimiang12]

create function f_1(@id int)
returns varchar(4000)
as
begin
declare @s varchar(4000)
set @s=''
select @s=@s+','+list from tb where id=@id
return left(@s,len(@s)-1)
end
go
select id,list=dbo.f_1(id) from tb group by id

[zzit0721]

create function x (@a int)----建立函数
returns varchar(20)
as
begin
declare
@x varchar(20)
set @x=''
select @x=@x+b+','
from a
where a=@a
set @x=stuff(@x,len(rtrim(@x)),1,'')
return @x
end

create table a (a int,b varchar(10))----建立测试数据
insert into a select 1,'a'
insert into a select 1,'b'
insert into a select 1,'c'
insert into a select 1,'d'
insert into a select 2,'a'
insert into a select 2,'d'
insert into a select 2,'e'
insert into a select 3,'d'
insert into a select 3,'i'
insert into a select 3,'k'
insert into a select 4,'c'
insert into a select 4,'s'


select a,dbo.x(a)list from a
group by a

drop table a
drop function x

---结果
---1 a,b,c,d
---2 a,d,e
---3 d,i,k
---4 c,s

[iwl]

Create Function F_GetStr(@id)
returns varchar(200)
as
begin
declare @s varchar(200)
set @s=''
select @s=','+list+'' from
(select list from 用例表 where id=@id) A
set @s=stuff(@s,1,1,'')
return @s
end

--查询
select ID,F_GetStr(ID) from 用例表
group by ID

[lfh103856111]

up

[samfeng_2003]

只有up了 :)

[wjj3000]

谢谢大家