select a.id1, sortId = (select count(*) from #t1 where id1=a.id1 and sortId<=a.sortId), a.wName from #t1 a
create table t1 ( id1 int, sortId int, wName varchar(100) ) insert t1 select 1,1,'钳' union all select 1,2,'电' union all select 1,3,'焊' union all select 2,1,'钳' union all select 2,3,'焊'select a.id1, sortId = (select count(*) from t1 where id1=a.id1 and sortId<=a.sortId), a.wName from t1 a id1 sortId wName ---- ------ -------- 1 1 钳 1 2 电 1 3 焊 2 1 钳 2 2 焊
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE Function GetA1_CHAR2(@ID int) Returns Varchar(100) AS Begin declare @str varchar(50) set @str='' select @str=@str+','+convert(varchar(10),sortid) from t1 where id1=@ID Return right(@str,len(@str)-1) /* Declare @S Varchar(100) Set @S='' Select @S=@S+A1_CHAR from t1 Where ID=@ID Order By A1_CHAR Return @S */ EndGO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO --测试 select id1,dbo.GetA1_CHAR2(id1) from t1 group by id1 --结果 id1 ----------- ---------------------------------------------------------------------------------------------------- 1 1,2,3 2 1,3
a.id1,
sortId = (select count(*) from #t1 where id1=a.id1 and sortId<=a.sortId),
a.wName
from
#t1 a
(
id1 int,
sortId int,
wName varchar(100)
)
insert t1
select 1,1,'钳'
union all
select 1,2,'电'
union all
select 1,3,'焊'
union all
select 2,1,'钳'
union all
select 2,3,'焊'select
a.id1,
sortId = (select count(*) from t1 where id1=a.id1 and sortId<=a.sortId),
a.wName
from
t1 a
id1 sortId wName
---- ------ --------
1 1 钳
1 2 电
1 3 焊
2 1 钳
2 2 焊
GO
SET ANSI_NULLS OFF
GO
CREATE Function GetA1_CHAR2(@ID int)
Returns Varchar(100)
AS
Begin
declare @str varchar(50)
set @str=''
select @str=@str+','+convert(varchar(10),sortid) from t1 where id1=@ID
Return right(@str,len(@str)-1)
/*
Declare @S Varchar(100)
Set @S=''
Select @S=@S+A1_CHAR from t1 Where ID=@ID Order By A1_CHAR
Return @S
*/
EndGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--测试
select id1,dbo.GetA1_CHAR2(id1) from t1 group by id1
--结果
id1
----------- ----------------------------------------------------------------------------------------------------
1 1,2,3
2 1,3