本人遇到一个SQL查询问题,请高手们指点.
题目如下:
假如原表为:
id firstname lastname
1 jim king
1 tom huo
2 jun li
2 ming wang
我要得到如下结果:
id name
1 king/jim,huo/tom
2 li/jun,wang/ming
题目如下:
假如原表为:
id firstname lastname
1 jim king
1 tom huo
2 jun li
2 ming wang
我要得到如下结果:
id name
1 king/jim,huo/tom
2 li/jun,wang/ming
INSERT tb SELECT 1,'jim','king'
union all select 1,'tom','huo'
union all select 2,'jun','li'
union all select 2,'ming','wang' GO--合并处理函数
CREATE FUNCTION dbo.f_str(@id varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+lastname+'/'+firstname
FROM tb
WHERE id=@id
RETURN(STUFF(@re,1,1,''))
END
GO--调用函数
SELECT id,name=dbo.f_str(id) FROM tb GROUP BY id
--删除测试
DROP TABLE tb
DROP FUNCTION f_str
/*--结果
col1 col2
---------- -----------
a 1,2
b 1,2,3
--*/
GO
INSERT tb SELECT 1,'jim','king'
union all select 1,'tom','huo'
union all select 2,'jun','li'
union all select 2,'ming','wang' GO--合并处理函数
CREATE FUNCTION dbo.f_str(@id varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+lastname+'/'+firstname
FROM tb
WHERE id=@id
RETURN(STUFF(@re,1,1,''))
END
GO--调用函数
SELECT id,name=dbo.f_str(id) FROM tb GROUP BY id
--删除测试
DROP TABLE tb
DROP FUNCTION f_str
--建立測試環境
Create Table TEST
(id Int,
firstname Varchar(100),
lastname Varchar(100))
--插入數據
Insert TEST Select 1, 'jim', 'king'
Union All Select 1, 'tom', 'huo'
Union All Select 2, 'jun', 'li'
Union All Select 2, 'ming', 'wang'
GO
--建立函數
Create Function GetName(@ID Int)
Returns Varchar(1000)
As
Begin
Declare @S Varchar(1000)
Set @S=''
Select @S=@S+firstname+'/'+lastname+',' from TEST Where ID=@ID
Return (Left(@S,Len(@S)-1))
End
GO
--測試
Select ID,dbo.GetName(ID) As Name from TEST Group By ID Order By ID
--刪除測試環境
Drop Table TEST
Drop Function GetName
--結果
/*
ID Name
1 jim/king,tom/huo
2 jun/li,ming/wang
*/