asked on

Write result records horizontal instead of vertical

hi,
i have this simple select statement:
select ReportName, Recipient, Freq from ReportRecipients
WHERE ((ReportRecipients.Freq)='D') OR ((ReportRecipients.Freq)='S') ORDER BY ReportRecipients.ReportName;

and what it is returning are simple records like this

ReportName Recipient Freq
rpt01Daily      Jason       D
rpt01Daily      Joseph       D
rpt01Daily      Linda       D
rpt01Daily      Steve       D
rpt01Daily      Bill D
rpt03      Lauren       D
rpt03      Edison       D
rpt03      Linda       D
rpt03      Fabio       D
rpt03      Bill       D
rpt03      Jose       D
rpt04      Salvatore       D
rpt04      Debbie       D
rpt04      Colleen       D

and what i need to do is to make the query return the records like this:

ReportName Recipient Freq
rpt01Daily      Jason ; Joseph ; Linda; Steve; Bill      D
rpt03      Lauren ; Edison ; Linda; Fabio; Bill, Jose      D
rpt04      Salvatore ;Debbie ; Colleen      D

Can this be done?

paelo

You could create a function to aggregate the string, such as:

create function dbo.udfRecipients (
@Rpt varchar(50),
@Freq varchar(10)
)
returns varchar(200)
As

Declare @s varchar(200)
select @s=COALESCE(@s+'; ','')+Recipient
from ReportRecipients
where ReportName=@Rpt and Freq=@Freq

return @s

end function

select ReportName, Freq, dbo.udfRecipients(ReportName,Freq)
from ReportRecipients
where ((Freq)='D') or ((Freq)='S')
group by ReportName, Freq
order by ReportName

jsctechy

ASKER

i got this error:
Msg 156, Level 15, State 1, Procedure udfRecipients, Line 8
Incorrect syntax near the keyword 'Declare'.
Msg 156, Level 15, State 1, Procedure udfRecipients, Line 15
Incorrect syntax near the keyword 'function'.

ASKER CERTIFIED SOLUTION

paelo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial