ASP.NET - how create sp for this

table 1 have
memberid(autoincr) name addresss
1                 aa   xxxxx
2                bb    yyyyy
table 2 have
GID(autoincr)  businessid  groupname
1              9          Group1
2             9          group2
i have create fuction output like this

businessid   memberid   groupid
 9            1          1
9           2          1
9           1         2

i want output

memberid  name address  groupname

1          aa   xxxxx   group1,group2
2          bb    yyyyy    group1

did you send me your functin?

Hi..

here is the stored procedure i have written try to make use of it..

CREATE PROCEDURE sp_MYSP

AS

WITH testAS (

select T1.memberid,T1.Name,t1.Address,T2.GroupName, from table1 T1

join Table3 T3 on T1.MemberId=T3.MemberId

join Tabe2 on T3.groupId=T2.GID)

select tbl. memberid, tbl.Name,tbl.Address,
substring(
(
SELECT ',' + tbl.GroupName AS [text()]
FROM #test as t
WHERE t.memberid = tbl.memberid
ORDER BY membered,name,address,groupname
FOR XML PATH(''), type).value('(/text())[1]','varchar(max)'), 2, 1000)
from #test as tbl
group by tbl.memberid

Go

Hi Frndz,

 

Functionality: Merge field name with comma separator  

 

To achieve this task

Use one fuction that returns merge value with comma separator

 

Use COALESCE to merge group name

 

Logic:

 

Call JoinGroup Name function

 

select *,dbo.JoinGropupName(tbl.memberid) as groupname

from Table1 as tbl

 

Function

 

ALTER FUNCTION [dbo].[JoinGropupName](@MemberID int)

 

RETURNS VARCHAR(MAX)

AS

BEGIN

    -- Declare the return variable here

    DECLARE @ReturnValue as Varchar(MAX)

    select

        @ReturnValue= COALESCE(@ReturnValue + ',', '') + grp.GroupName 

    from

        Table12 as Config

        inner join Table1 as mem on Config.memberid = mem.memberid

        inner join Table2 as grp on Config.groupid = grp.GID

    where

        mem.memberid = @MemberID

   -- Return the result of the function

    RETURN @ReturnValue

 

END

 

 

Hope this helpful!

Thanks