?
Solved

Write result records horizontal instead of vertical

Posted on 2007-08-10
3
Medium Priority
?
241 Views
Last Modified: 2013-11-05
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?
0
Comment
Question by:jsctechy
  • 2
3 Comments
 
LVL 9

Expert Comment

by:paelo
ID: 19674528
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
0
 
LVL 1

Author Comment

by:jsctechy
ID: 19674561
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'.
0
 
LVL 9

Accepted Solution

by:
paelo earned 2000 total points
ID: 19674565
Yes, sorry, messed up the syntax a bit.  Use this for the function:

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

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

return @s

end
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question