sql合并查询
例如：Table
Id name
1 21
1 22
1 23
2 24
2 25
怎么用SQL输出为
1 21,22,23
2 24,25
谢谢了
[解决办法]
--創建函數
Create Function F_GetName(@Id Int)
Returns Varchar(2000)
As
Begin
Declare @S Varchar(2000)
Select @S = ' '
Select @S = @S + ', ' + name From TEST Where Id = @Id
Select @S = Stuff(@S, 1, 1, ' ')
Return @S
End
GO
--測試
Select
id,
dbo.F_GetName(id) AS name
From
TEST
Group By
id
[解决办法]
--創建環境
Create Table TEST(Id Int, name Nvarchar(30))
--插入數據
Insert TEST Select 1, N '21 '
Union All Select 1, N '22 '
Union All Select 1, N '23 '
Union All Select 2, N '24 '
Union All Select 2, N '25 '
GO
--創建函數
Create Function F_GetName(@Id Int)
Returns Varchar(2000)
As
Begin
Declare @S Varchar(2000)
Select @S = ' '
Select @S = @S + ', ' + name From TEST Where Id = @Id
Select @S = Stuff(@S, 1, 1, ' ')
Return @S
End
GO
--測試
Select
id,
dbo.F_GetName(id) AS name
From
TEST
Group By
id
GO
--刪除測試環境
Drop Table TEST
Drop Function F_GetName
--結果
/*
idName
121,22,23
224,25
*/
