求一简单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
--結果
/*
Type Name
78 $Abc$BCV$IKL
90 $IJK$OIP
*/