SQL 中多行转为一行

Create   table  Emp_Announcement_Account
(
AnnouncementID  Int   not   Null ,
AccountNum  Int   Not   Null ,
SubAccount  Int   Not   Null
)
Alter   Table  Emp_Announcement_Account
Add   Constraint  PK_Emp_Announcement_Account  PRIMARY   KEY (AnnouncementID,AccountNum,SubAccount)

insert   into  Emp_Announcement_Account  values ( 123 , 789 , 0 )
insert   into  Emp_Announcement_Account  values ( 123 , 789 , 1 )
insert   into  Emp_Announcement_Account  values ( 123 , 12313 , 2 )


Create   Function  UDF_SIS30_BO_RMSAnnouncement_MergSpecifiedMembers
(
@AnnouncementID   Int
)  Returns   Varchar ( 8000 )
As
Begin
Declare   @temp   Varchar ( 1000 )
Set   @temp = ''   -- 设置为空,如果不设置则返回 NULL

-- 连接成一行
Select   @temp = @temp + ' , ' + cast (AccountNum    as     varchar ) + ' - ' + Cast (SubAccount  as   varchar )
From  Emp_Announcement_Account  Where  AnnouncementID = @AnnouncementID
-- 去掉第一个','
If   len ( @temp ) > 0
     Set   @temp =Right ( @temp , len ( @temp ) - 1 )
Return   @temp
End

转载于:https://www.cnblogs.com/chinabc/archive/2009/11/04/1596077.html