Sql的自定义聚合函数，挺费劲。

想实现把字符串合并的聚合函数。关键是在视图中不能定义变量(在函数中可以定义变量)。不能进行复杂的多语句操作。

但也有解决方案 见： http://www.cnblogs.com/yiyanxiyin/archive/2008/09/28/1230524.html

 

一条语句出结果，挺费劲，我的实现。

 

CREATE TABLE [dbo].[PowerAction](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Action] [varchar](50) NULL,
    [ControllerID] [int] NULL,
 CONSTRAINT [PK_RoleAction] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
 

CREATE TABLE [dbo].[PowerButton](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ActionID] [int] NULL,
    [Button] [varchar](50) NULL,
 CONSTRAINT [PK_RoleButton] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)  ON [PRIMARY]
) ON [PRIMARY]

GO

SELECT *
FROM(
    select distinct a.ID  ,a.[Action] 
    from  PowerAction as a
    left join PowerButton as b on (b.actionid = a.id)
     
) t
OUTER APPLY(
    SELECT
        [Button]= STUFF(REPLACE(REPLACE(
            (
                select isnull(b.Button,'') as Button
                from  PowerAction as a
                left join PowerButton as b on (b.actionid = a.id)
                 
                WHERE a.id = t.id
                FOR XML AUTO
            ), '<a Button="', ','), '"/>', ''), 1, 1, '')
)N

 

表值函数定义方法：

create function test(@i int )
returns @retVal table(id int ,val varchar(3000) )
as 
begin     
      select * from  @retVal  ; 
      return ;
end ;