求一简单SQL语句?
表a如下
ID Name Type
1 Abc 78
2 BCV 78
3 IJK 90
4 IKL 78
4 OIP 90
要得到如下表
Type Name
78 $Abc$BCV$IKL
90 $IJK$OIP
[解决办法]
--創建函數
Create Function F_TEST(@Type Int)
Returns Varchar(1000)
As
Begin
Declare @S Varchar(1000)
Select @S = ' '
Select @S = @S + '$ ' + Name From a Where Type = @Type
Return @S
End
GO
--測試
Select
Type,
dbo. F_TEST(Type) As Name
From
a
Group By
Type
[解决办法]
--1.创建一个合并的函数
create function fmerg(@type char(8))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str= ' '
select @str=@str+ ', '+ name from 表A where type=@type
set @str=right(@str,len(@str)-1)
return(@str)
End
go
--调用自定义函数得到结果
select distinct type,dbo.fmerg(type) from 表A
[解决办法]
--創建測試環境
Create Table a
(ID Int,
Name Varchar(100),
Type Int)
Insert a Select 1, 'Abc ', 78
Union All Select 2, 'BCV ', 78
Union All Select 3, 'IJK ', 90
Union All Select 4, 'IKL ', 78
Union All Select 5, 'OIP ', 90
GO
--創建函數
Create Function F_TEST(@Type Int)
Returns Varchar(1000)
As
Begin
Declare @S Varchar(1000)
Select @S = ' '
Select @S = @S + '$ ' + Name From a Where Type = @Type Order By ID
Return @S
End
GO
--測試
Select
Type,
dbo. F_TEST(Type) As Name
From
a
Group By
Type
GO
--刪除測試環境
Drop Table a
Drop Function F_TEST
--結果
/*
TypeName
78$Abc$BCV$IKL
90$IJK$OIP
*/
