Tableid name title
1 AA 1.AA
2 BB 2.BB
3 CC 3.CC
1 AA 1.BB
我要把相同ID,NAME的title 合併成一行顯示結果為
id name title
1 AA 1.AA,1.BB
2 BB 2.BB
3 CC 3.CC
1 AA 1.AA
2 BB 2.BB
3 CC 3.CC
1 AA 1.BB
我要把相同ID,NAME的title 合併成一行顯示結果為
id name title
1 AA 1.AA,1.BB
2 BB 2.BB
3 CC 3.CC
解决方案 »
- 关于UpdatePanel1 不解求助 分不够再加
- asp.net 用户控件脚本函数问题
- 关于网站多语言,谁能给一个详尽的方案。谢谢~
- 熟悉java和asp,想学asp.net,怎么学啊?
- 急!!!!asp.net如何去掉日期中的年,还有,我想让月份和日期如过是一位数字的话...
- 有关aspx页面自定义错误更改问题
- 在客户端用javascript设置span的innerHTML的值,但是后台的label.Text却没有得到值??
- 急急!!急急!!急急!!,求教CSDN上的高手SQL语句中的单引号问题!!!!
- 关于数据库的一个问题
- 求大神解惑
- 为什么文件名长度被截断?
- FreeTextBox怎么实现自动保存输入内容到剪贴板?
Create Table TEST
(id Int,
name Varchar(10),
title Varchar(10))
Insert TEST Select 1, 'AA', '1.AA'
Union All Select 2, 'BB', '2.BB'
Union All Select 3, 'CC', '3.CC'
Union All Select 1, 'AA', '1.BB'
GO
--建立合併函數
Create Function Gettitle(@id Int, @name Varchar(10))
Returns Varchar(8000)
As
Begin
Declare @S Varchar(8000)
Select @S = ''
Select @S = @S + ',' + Rtrim(title) From TEST Where id =@id And name = @name
Select @S = Stuff(@S , 1, 1 ,'')
Return @S
End
GO
--測試
Select
id,
name,
dbo.Gettitle(id, name) As title
From
TEST
Group By
id,
name
GO
--刪除測試環境
Drop Table TEST
Drop Function Gettitle
--結果
/*
id name title
1 AA 1.AA,1.BB
2 BB 2.BB
3 CC 3.CC
*/
create function GetTitle
(@id int,@name varchar(30))
returns varchar(1000)
as
begin
declare @title varchar(1000)
set @title=''
select @title=@title + ',' + title from t1 where id=@id and name=@name
set @title=stuff(@title,1,1,'')
return @title
end---获取数据
select id,name,dbo.getTitle(id,name) from t1
group by id,name
正解
Select @S = ''
Select @S = @S + ',' + Rtrim(title) From TEST Where id =@id And name = @name
Select @S = Stuff(@S , 1, 1 ,'')
Return @S
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创建一个合并的函数
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)
End
go--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A
請問這個函數寫在哪阿?我不會用啊
Create Function Gettitle(@id Int, @name Varchar(10))
Returns Varchar(8000)
As
Begin
Declare @S Varchar(8000)
Select @S = ''
Select @S = @S + ',' + Rtrim(title) From TEST Where id =@id And name = @name
Select @S = Stuff(@S , 1, 1 ,'')
Return @S
End
GO--當然,這裡的TEST要改為你實際的表名
id,
name,
dbo.Gettitle(id, name) As title
From
TEST
Group By
id,
name-- 同樣,TEST要改為你自己實際的表名.
Result returns varchar2(4000);
begin
declare @attname varchar2(4000)
set @attname=''
select @attname=@attname + ',' + att_name from pur_std_data where first_cate=@first_cate and second_cate=@second_cate
set @attname=stuff(@attname,1,1,'')
return @attname;
end Getattname;可是編譯的時候沒成功
Result varchar2(4000);
begin
set Result=''
select Result=Result + ',' + att_name from pur_std_data where first_cate=@first_cate and second_cate=@second_cate
set Result= SUBSTR(Result, 2, LENGTH (Result) - 1)
return Result;
end ;
好人吶-_-我去試試